If Statement in SQL?

Arc

Hey guys, I have a table that has a field called Status. There are two possibilities for this field..."Active" or "Inactive".

I have a Change status button on my form and I want to be able to reverse what is in that field based on whether a check box was checked.

Detecting if the check box was checked is no problem, but How do i reverse what is in the Status field?

For instance, if it has a Value of "Active" then I want to change it to "Inactive".

$SQL = "UPDATE tblUsers 
(IF Status = "Active" THEN SET Status = "Inactive") || 
(IF Status = "Inactive" THEN SET Status = "Active")  

WHERE UserID = '$UserID'";

Of course that doesnt work, but I hope you see what I mean.

Thanks! :o

michaelkenneth

Hi,

What you want to do, is reverse it before your sql statement in/with php.
You can do some thing like this

$status = $_POST['status'];
if($status == "Active")
{
$status = "Inactive"
}
if($status == "Inactive")
{
$status = "Active"
}
 $SQL = "UPDATE tblUsers
set status = '$status'
WHERE UserID = '$UserID'";

I am almost positive that sql statement will not work.. and the code above it might not even work.. I am just showing you how you should go about doing it.

-Mike

Weedpacket

Depending on which DBMS you're using you should be able to use an IF or CASE statement.

E.g., http://dev.mysql.com/doc/mysql/en/control-flow-functions.html or http://www.postgresql.org/docs/8.0/interactive/functions-conditional.html or whichever documentation is relevant to you.

Oersoep

IF( condition, return_if_true, return_if_false )

$SQL = "UPDATE tblUsers SET Status = IF( Status = 'Active', 'Incactive', IF( Status = 'Inactive', 'Active', Status ) ) WHERE UserID = '$UserID'";

If the value is neither Active or Inactive it stays the same.

!!! Don't know if this will work on other databases than MySQL !!!

Arc

Thanks Oer! Thats exactly what I was looking for!