I tend to use a lot of tinyint columns as controllers for my database-held objects, usually sticking to the convention of 1 means on and 0 means off. In other words, the perfect target for a SQL toggle statement!
In order to toggle update a value in SQL, we’ll need to make use of the common SQL control flow function IF. The IF function takes three parameters, the first being the test expression, the second being the term to return if the test expression passes and the third being the term returned if the test expression fails.
So putting this into our 1 or 0 toggle SQL statement, we get:
UPDATE `table` SET `column` = IF(`column` = 1, 0, 1) WHERE `id` = x
It’s pretty intuitive to see what is going on above, now that we understand the form of the fabulous IF SQL function!
Nifty.
Related Link: http://dev.mysql.com/doc/refman/5.1/en/control-flow-functions.html#function_if
You might also enjoy:
-
There exists in this world a nice little SQL statement know as the SELECT INTO statement, one that works beautifully well in most database systems for when ... -
Annoyingly, I got saddled with a database for a system which contained a whole lot of "uncleaned data", in other words data with a lot of trailing and leadi ... -
Just a simple one this time around to give you the syntax for adding optional input variables to your PHP functions. This is pretty useful if you are going ... -
I stumbled across this gem of a quick guide to Bash shell scripting over at some or other forum and so as not to lose this valuable resource for my later us ... -
Sometimes you need to store things like file paths into a database table during your PHP script's execution. However, on going back to the database after ru ...
