• 0 Vote(s) - 0 Average
  • 1
  • 2
  • 3
  • 4
  • 5
Tip: toggling a boolean database column

#1
[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!

#2
[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.

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


Digg   Delicious   Reddit   Facebook   Twitter   StumbleUpon  


  Theme © 2014 iAndrew  
Powered By MyBB, © 2002-2021 MyBB Group.