How to write IF ELSE statement in MySQL query

Question

How to write IF ELSE statement in MySQL query?

Something like that:

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

Then in my array, I can 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 should write it in SQL, not in C / PHP style

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

for use in request

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

for use in stored procedures or functions

Answer 3

You are looking for case :

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

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

Note that as state is just a column overlay. I assume 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 be the same ....

Answer 5

according to the mySQL reference, this is the syntax for using the if and else statement:

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

So, regarding your request:

 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 a good example in this link: http://easysolutionweb.com/sql-pl-sql/how-to-use-if-and-else-in-mysql/