How do write IF ELSE statement in a MySQL query

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

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;

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

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.

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

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

both results will same....

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/

Related