How can I use the if statement in MYSQL query to return yes if it's true and no if it's not?
See the example :
IF(table_name.field_name_value = 1, 'yes','no') AS active
I'm trying something like this but with no success
-
What do you mean by " with no success "? You wanted to say that " this is not working "?Tadeck– Tadeck2011-11-13 13:33:32 +00:00Commented Nov 13, 2011 at 13:33
-
Tell us how it works and what is the expected result, if you want your problem to be solved for you, please.Tadeck– Tadeck2011-11-13 13:45:34 +00:00Commented Nov 13, 2011 at 13:45
-
@Tadeck, when i print active it doesn't show anythingrevo– revo2011-11-13 13:49:59 +00:00Commented Nov 13, 2011 at 13:49
-
@Tadeck,print_r($array['active']);revo– revo2011-11-13 13:53:06 +00:00Commented Nov 13, 2011 at 13:53
-
Your problem may be more complex and may not be related to this expression. The query works - see my updated answer.Tadeck– Tadeck2011-11-13 13:57:05 +00:00Commented Nov 13, 2011 at 13:57
|
Show 1 more comment
4 Answers
From the documentation on IF() function:
mysql> SELECT IF(1<2,'yes','no');
-> 'yes'
So, basically, the syntax is as mentioned above.
EDIT:
You are saying my example is incorrect. Thus, I provide you a proof (which proves the example from the documentation I mentioned above):
I have a table, which has uid column, containing ID of the user. The table is part of the database on some site (which is not relevant). When I make the following query:
SELECT `uid`, IF(`uid`=3, 'yes', 'no') AS `active` FROM `mysite_users`;
I receive the following result:
+-----+--------+
| uid | active |
+-----+--------+
| 0 | no |
| 1 | no |
| 3 | yes |
| 8 | no |
| 9 | no |
| 10 | no |
| 11 | no |
| 12 | no |
| 13 | no |
| 14 | no |
| 15 | no |
+-----+--------+
11 rows in set (0,00 sec)
Which is exactly what I would expect (and what should be expected after reading the documentation). Is it still not working for you?
4 Comments
revo
i'm trying to compare a field value with a static number, as i said above. my example is not correct so yours either!
Tadeck
@Revo: It is not my example, it is an example from the documentation (see page linked within my answer), so how could it be incorrect? Have you even checked it?
revo
the problem is using AS !
Tadeck
@Revo: See the updated answer. This is certainly working - I have tested it twice and it works exactly at the documentation says.
You can use case function:
select case
when table_name.field_name_value = 1 then 'yes'
else 'no'
end case as active
from table_name
Although if function should also work. Can you post your full query?
answered Nov 13, 2011 at 13:31
Pablo Santa Cruz
182k33 gold badges250 silver badges300 bronze badges
Comments
You can use CASE in mysql query for that. Like
CASE WHEN table_name.field_name_valud = 1 THEN 'Yes' ELSE 'No' END As active
answered Nov 13, 2011 at 13:31
Sudhir Bastakoti
100k15 gold badges163 silver badges169 bronze badges
Comments
See http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html
SELECT CASE WHEN table_name.field_name_value = 1 THEN 'yes'
ELSE 'no' END;
