using if else in sql query

[o0O0o.o0O0o]

I want to use query so that if column = 1 then it should update to 0 and if 0 then update to 1.


something like

UPDATE userTasks SET completed = if (completed) > 0 (1,0)
WHERE

id ='$task_id'

[abduraooft]

Try
UPDATE userTasks SET completed =-(completed-1)
WHERE id =$task_id

PS: Don't enclose your integer values by quotes. Instead validate them in PHP to assure their values. You may use ctype_digit()

[Fumigator]

Clever solution! BTW the PHP function you should use is is_numeric() which will allow for decimal point, negative sign, etc. where ctype_digit() must contain only numbers.

[Old Pedant]

Ummm...I think that for an *ID* (see code... id=$task_id) that indeed ctype_digit might well be the better choice.

And the alternative answer, that the original poster was trying to do, is:

UPDATE userTasks SET completed = IF(completed > 0, 0, 1 )
WHERE id =$task_id

The problem with using
UPDATE userTasks SET completed =-(completed-1)
WHERE id =$task_id

or the simpler rewrite as

UPDATE userTasks SET completed = 1 - completed
WHERE id =$task_id

is that if somehow the value ever ends up as anything *other* than 0 or 1 then your code isn't self-correcting.

Of course, if you used a *BOOLEAN* for the completed field, instead of an integer, then indeed

UPDATE userTasks SET completed = NOT completed
WHERE id =$task_id

And even though a boolean is a tinyint in MySQL, and so *could* get a value greater than 1, the NOT operator takes care of that for you.

[o0O0o.o0O0o]

completed =-(completed-1)

he he thats was intelligent answer :D

Thanks everyone