5

I need to return true or false rather than 1 & 0, using following query:

select if(u.id is null,false,true) status
from user u
limit 10

the above query returns status with value 0 or 1 rather than true and false,

Is there any way to fix this?

mwafi
  • 3,946
  • 8
  • 56
  • 83

5 Answers5

9

If you want, you can return the values as strings:

SELECT IF(u.id IS NULL, 'false', 'true') as status
FROM user u
LIMIT 10
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
4

TRUE/FALSE is equivalent to 1/0. It's just a matter of how your front end displays it.

If you need to return the strings "true" and "false" (which I don't suggest - handle that in the display) then you'll have to account for that as well:

IF(IF(u.id ISNULL,false,true) = 1, 'TRUE', 'FALSE')

Tom H
  • 46,766
  • 14
  • 87
  • 128
  • 1
    Is there a way to return them as `true` & `false` without convert them to strings? – mwafi Feb 29 '16 at 13:32
  • 3
    As I said, 1/0 **is** true/false, so your original statement was already returning them as true/false. It's just a matter of what you want to do with that in your front end. I would try to leave it as true/false and **not** convert it to strings. Let your front end or other code handle what it does with the 1/0 (true/false). – Tom H Feb 29 '16 at 13:42
3

MySQL has no boolean datatype, so you need to stick with 0 and 1 on the MySQL side:

select if(u.id is null, 0, 1) status_int
from user u
limit 10

If you prefer a boolean over 0/1 in PHP, you can cast it like this: 

$status = (bool) $status_int;
simon
  • 2,896
  • 1
  • 17
  • 22
0

I just had a similar issue in lua. For some reason if the table column type is "tinyint" and you call 1 or 0 it will return as true and false. If you change the column type to "int" it will call and return in lua as 1 and 0.

Old post but I hope this helps someone!

ELJRP.NET
  • 1
-1

IF()

You are missing single quotes.

select if(u.id is null,'false','true') status
from user u
limit 10;
Ullas
  • 11,450
  • 4
  • 33
  • 50