CodeIgniter Forums
Tip: toggling a boolean database column - Printable Version

+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: Tip: toggling a boolean database column (/showthread.php?tid=20128)



Tip: toggling a boolean database column - El Forum - 06-29-2009

[eluser]richthegeek[/eluser]
Hi,

If you ever need to toggle a boolean database column between 1 and 0, but don't know what value is at the time, you can use the following query:
Code:
UPDATE `table` SET `column` = MOD( `column` + 1, 2 ) WHERE ...

It saves running a select query Smile

Hope that helps someone!


Tip: toggling a boolean database column - El Forum - 06-29-2009

[eluser]TheFuzzy0ne[/eluser]
Nice idea, I like it. Thanks for posting. I'd just like to add, that for anyone using the Active Record library, you'll need to be careful, as the query probably won't be escaped correctly. This is what I had to do in my model to stop it from happening.

Code:
function _update($forum)
    {
        $this->db->where('id', $forum['id']);
        
        foreach ($forum as $key => $val)
        {
            $escape = TRUE;
            if (preg_match('/^([a-z0-9_]+)\s?\+\s?\d+$/i', $val, $matches))
            {
                if (isset($this->fields[$matches[1]]))
                {
                    $escape = FALSE;
                }
            }
            
            $this->db->set($key, $val, $escape);            
        }
        
        unset($forum);
        
        return $this->db->update($this->table);
    }

I had to loop through the data array, and set each field separately, after checking whether or not it needed escaping. It may need modifying to work with the code above, however.


Tip: toggling a boolean database column - El Forum - 07-03-2009

[eluser]sophistry[/eluser]
the bitwise operator XOR would work for this too :-)
Code:
UPDATE mytable SET column=1 XOR column WHERE [condition];