IFNULL is not working

问题:

Hi I am working with mysqli to replace a default value on the table if the data from the database is NULL. I already tried it on PHPmyAdmin and it's working but not on my code :(

Here's my SELECT query:

$query="SELECT pro_id, pro_name, unit_name, cat_name, IFNULL(quantity,'empty') AS quantity FROM products, unit, categories WHERE products.unit=unit.unit_id AND products.pro_cat=categories.cat_id";

回答1:

If, as one of your comments seems to indicate, the error you're getting is:

Incorrect parameter count in the call to native function 'ISNULL'

then it's a simple typo. ISNULL is not the same as IFNULL.

The former returns a truth value if its one argument is null.

The latter returns the second argument if the first is null, otherwise it returns the first argument.

You can see this if you put the following code into SqlFiddle:

-- DDL
create table xyzzy (plugh int);
insert into  xyzzy (plugh)       values (null);
insert into  xyzzy (plugh)       values (42);

select plugh, isnull(plugh)    from xyzzy;
select plugh, ifnull(plugh,-1) from xyzzy;
select plugh, isnull(plugh,-1) from xyzzy;

The output is as expected for the first two select statements while the third generates the error you describe:

plugh   isnull(plugh)
------  -------------
(null)  1
42      0

plugh   ifnull(plugh,-1)
------  ----------------
(null)  -1
42      42

Incorrect parameter count in the call to native function 'isnull'