How do write IF ELSE statement in a MySQL query

Question

How do I write an IF ELSE statement in a MySQL query?

Something like this:

mysql_query("...(irrelevant code).. IF(action==2&&state==0){state=1}");

Then down in my array I should be able to do this:

 $row['state'] 
//this should equal 1, the query should not change anything in the database, 
//just the variable for returning the information

Answer 1

You probably want to use a CASE expression.

They look like this:

SELECT col1, col2, (case when (action = 2 and state = 0) 
 THEN
      1 
 ELSE
      0 
 END)
 as state from tbl1;

Answer 2

you must write it in SQL not it C/PHP style

IF( action = 2 AND state = 0, 1, 0 ) AS state

for use in query

IF ( action = 2 AND state = 0 ) THEN SET state = 1

for use in stored procedures or functions

Answer 3

You're looking for case:

case when action = 2 and state = 0 then 1 else 0 end as state

MySQL has an if syntax (if(action=2 and state=0, 1, 0)), but case is more universal.

Note that the as state there is just aliasing the column. I'm assuming this is in the column list of your SQL query.

Answer 4

SELECT col1, col2, IF( action = 2 AND state = 0, 1, 0 ) AS state from tbl1;

OR

SELECT col1, col2, (case when (action = 2 and state = 0) then 1 else 0 end) as state from tbl1;

both results will same....

Answer 5

according to the mySQL reference manual this the syntax of using if and else statement :

IF search_condition THEN statement_list
[ELSEIF search_condition THEN statement_list] ...
[ELSE statement_list]
END IF

So regarding your query :

x = IF((action=2)&&(state=0),1,2);

or you can use

IF ((action=2)&&(state=0)) then 
state = 1;
ELSE 
state = 2;
END IF;

There is good example in this link : http://easysolutionweb.com/sql-pl-sql/how-to-use-if-and-else-in-mysql/