IF statement in Query Builder Select, odd results?

I am using an IF statement in a QB select, as follows:

PHP Code:

$this->db->select("details.*, IF(favourites.id, TRUE, FALSE) as favourite", FALSE); 


Firstly, this only works with the second parameter set to false for some reason.

Secondly, and to the point, the results I get back have either 1 or 0, not TRUE or FALSE.

Is this correct? Is there any way to have it so it is TRUE or FALSe, or maybe 1 and NULL?

It only works when the second parameter is FALSE because QueryBuilder will probably mangle the query when it tries to protect table and field names. If you still want to protect the table/field names on other parts of the select statement, you should be able to do something like this:

Code:

$this->db->select("details.*")
     ->select("IF(favourites.id, TRUE, FALSE) as favourite", FALSE);

MySQL's IF() function only returns a numeric or string value. In this case, you're probably lucky that it didn't just dump an error message. You might get the result you're looking for by using 'TRUE' and 'FALSE' as the arguments (instead of TRUE and FALSE), but you'll probably have to cast it to a boolean either way. If you want 1 or null, you should be able to use:

Code:

IF(favourites.id, 1, NULL)

Bonfire

Practical CodeIgniter 3
CodeIgniter Testing Guide