Insert php boolean into mysql bit column with Zend_Db

Question

I'm using Zend Framework 1.11.4 and also Zend_Db. The problem is, I have a column for sex which has the value 0 or 1(BIT(1)), when I put false the insertion is fine, but when I put true the following error appears: 'Data too long for column 'sex' at row 1 '

I already debugged and verified it's a boolean! With false(0) no error, but with true the error happens (Class Application_Model_UserNodeMapper):

public function save(Application_Model_UserNode $user){  
$sex = $user->getSex();  
if($sex == 'm'){  
    $user->setSex(false); //NO ERROR!!  
}  
else{  
        $user->setSex(true); //ERROR!!  
}
$data = $user->getProperties();   
if(null === ($id = $user->getId())) {    
    unset($data['id']);  
    $id = $this->getDbTable()->insert($data);
    return $id;             
} 
else{
   $this->getDbTable()->update($data, array('id = ?' => $id));  
   return null;
}

}  

Code for Application_model_UserNode(Some properties are in portuguese, I have changed sexo to sex to clarify things):

<?php


class Application_Model_UserNode
{
protected $id;
protected $nome_completo;
protected $nome_exibicao;   
protected $senha;
protected $status;
protected $email;
protected $sex;
protected $data_nasc;
protected $cidade_id;
protected $pais_id;


function __construct(array $options = null)
{
    if (is_array($options)) {
        $this->setOptions($options);
    }
}

public function __set($name, $value)
{
    $method = 'set' . $name;
    if (('mapper' == $name) || !method_exists($this, $method)) {
        throw new Exception('Invalid userNode property');
    }
    $this->$method($value);
}

public function __get($name)
{
    $method = 'get' . $name;
    if (('mapper' == $name) || !method_exists($this, $method)) {
        throw new Exception('Invalid guestbook property');
    }
    return $this->$method();
}

public function setOptions(array $options)
{
    $methods = get_class_methods($this);
    foreach ($options as $key => $value) {
        $method = 'set' . ucfirst($key);
        if (in_array($method, $methods)) {
            $this->$method($value);
        }
    }
    return $this;
}


public function getId() {
    return $this->id;
}

public function setId($id) {
    $this->id = $id;
}

public function getNome_completo() {
    return $this->nome_completo;
}

public function setNome_completo($nome_completo) {
    $this->nome_completo = $nome_completo;
}

public function getNome_exibicao() {
    return $this->nome_exibicao;
}

public function setNome_exibicao($nome_exibicao) {
    $this->nome_exibicao = $nome_exibicao;
}


public function getSenha() {
    return $this->senha;
}

public function setSenha($senha) {
    $this->senha = $senha;
}

public function getStatus() {
    return $this->status;
}

public function setStatus($status) {
    $this->status = $status;
}

public function getEmail() {
    return $this->email;
}

public function setEmail($email) {
    $this->email = $email;
}

public function getSex() {
    return $this->sex;
}

public function setSex($sex) {
    $this->sex = $sex;
}

public function getData_nasc() {
    return $this->data_nasc;
}

public function setData_nasc($data_nasc) {
    $this->data_nasc = $data_nasc;
}


public function getProperties(){
    $properties = get_object_vars($this);
    return $properties;
}


}

Thanks for the help!

Harrington

What is the data type of the column 'sex' and max size? What is the code for the class Application_Model_UserNode? It is possible that false was converted to '0' while true is kept as string 'true'! – Wherefore

Sex is defined as BIT(1) so it should accept both true and false. It accepts the value 1 if I edit direct from the database but not from application. I will post the Application_Model_UserNode code. – Harrington

#840096 – Diablerie

Your scripts works fine using PHP5.3, Zend Framework 1.11.11, and MYSQL 5.1.51. It is possible you have spelling mistake somewhere. or try using latest version of zend framework. Otherwise, using different datatype is safer option (tinyint). – Wherefore

Answer 1

The MySQL implementation of the bit datatype is not necessarily a single bit, but can vary between one and 64 bits at the time of the creation of the table. Most db connectors have difficulty with the datatype conversion because the MySQL bit is not actually a bit as you would colloquially think about it. The correct solution is to use a column type that is not bit, but tinyint(1), as indicated in your comment.

Sheree