Updating mysql bit datatype using codeigniter active record

I have a table with one columns datatype as BIT(1) . I am using active record of codeigniter for performing queries. But the setting of bit is not working. Does anybody has idea about how to do it? Or I have to get back to normal query?

Following is the code snippet:

function itemUpdate($options=array()) {
if(isset($options['isAvailable']))
   $itemDB->se开发者_C百科t('isAvailable',$options['isAvailable']);
   $itemDB->where('id', $options['id']);
   $itemDB->update('Item');
}

I often use:

$this->db->set('foo', (int) !empty($options['foo']));

Makes a 0/1 response pretty foolproof.

I've tried this using cakephp 1.3.10 framework (php 5.2.9 | mysql 5.1.33)

$decimal_value = hexdec(bin2hex($bin_value));

Works like a charm!

I just discovered this about Codeigniter, that the Active Record query builder automatically adds single quotes around the value, and that the MySQL BIT type won't accept the single quotes around a value.

To see what the actual generated query is, add this line immediately following your query: echo $this->db->queries[0];

You can also var_dump( $this->db );

Now you will see the problem with the query, but it's a problem that I think exists only because you're trying to set a BIT data type.

So, where this works:

// Given that $options['isAvailable'] = 12 and $options['id'] = 2
UPDATE Item SET `isAvailable` = 12 WHERE id=2

This fails:

UPDATE Item SET `isAvailable` = '12' WHERE id=2

...and it only fails because your column isAvailable is of type BIT.

Above, @phil-sturgeon gave us the answer, but we can just simplify it like so:

$itemDB->set('isAvailable', (int) $options['isAvailable']);
$itemDB->where('id', $options['id']);
$itemDB->update('Item');