[El Forum]

[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

Hope that helps someone!

[El Forum]

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

[El Forum]

[eluser]sophistry[/eluser]
the bitwise operator XOR would work for this too :-)

Code:

UPDATE mytable SET column=1 XOR column WHERE [condition];