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;
N.id and (CASE WHEN ...AS N.state to AS state.= instead of == for comparison?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;
OR
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/
