MYSQL left join if then statement favorite table procedure

Question

i have 2 tables
here is table 1 table name is forexample

itemlist

+-----+----------+-----+  
| uid | username | age |  
+-----+----------+-----+  
|   1 | doe      |  17 |  
|   2 | smith    |  18 |  
|   3 | john     |  30 |  
+-----+----------+-----+

and other one is

fav

+-----+------+---------+
| uid | user | itemuid |
+-----+------+---------+
|   1 | alex |       2 |
+-----+------+---------+

Here is my mysql query *NOT Working * any way to fix this problem when i run php file i got error in mysql syntax

SELECT c.uid, c.username, c.age,i.uid,i.user,i.itemuid 
from itemlist c 
left join fav i on c.uid = i.itemuid 
if (i.user = 'alex') THEN 
SET @fav = 1;
ELSE
SET @fav = 0;
END IF

this is sample php

while ($row = mysqli_fetch_array($res)){
    if ($row['fav'] = '1'){
            echo $row['username']." is exit in fav";
        }else{
            echo $row['username']." is not exit in fav";
    }
}

i hope you understand my question right ?

You want us to do your code for you? Is this the question? – ggdx Sep 08 '14 at 22:18 — ggdx, Sep 08 '14 at 22:18
Sorry Bro .... i just edit this question – user3242011 Sep 08 '14 at 22:22 — user3242011, Sep 08 '14 at 22:22
You can't use `IF...THEN...END IF` in a normal SQL query. – gen_Eric Sep 08 '14 at 22:23 — gen_Eric, Sep 08 '14 at 22:23
SO how it's done ? any solution – user3242011 Sep 08 '14 at 22:24 — user3242011, Sep 08 '14 at 22:24

score 1 · Accepted Answer · answered Sep 08 '14 at 22:37

To get a column named fav returned in the resultset, you would need to include an expression in the SELECT list, and give it an alias fav.

It's not at all clear why you would need a MySQL user-defined variable; if you don't know why you'd need one, then you probably don't need one.

Given that your PHP code is looking for a column named fav in the resultset, likely you want something like this:

SELECT c.uid
     , c.username
     , c.age
     , i.uid AS i_uid
     , i.user
     , i.itemuid
     , IF(i.user='alex',1,0) AS fav
  FROM itemlist c 
  LEFT
  JOIN fav i ON i.itemuid  = c.uid

Note that the original query had two columns named uid; if you want to return both, and be able to reference both of those by column name, you need to have distinct names for each. In the query above, I've assigned an alias to the i.uid column so that both uid columns will be available by distinct column name.

thank you sir you saved my time you are the best love you .... i don't know why people down my vote... — user3242011, Sep 08 '14 at 22:44
Sometimes, getting a simple example of how something is typically done is all that is needed. To me, it looks like you had done some research, and you tried something, and you were ready for help. Good luck! — spencer7593, Sep 08 '14 at 22:47

MYSQL left join if then statement favorite table procedure

1 Answers1