IFNULL() not working

[ethan.david.376]

"SELECT IFNULL(count(id), 0) AS ctr FROM table WHERE date >= $from_date AND date <= $to_date AND GROUP BY WEEK(table.date)"

This should result to 0 row. But, instead of printing 0, it is returning an empty array rather than {0, 0, 0, ... }

[cereal]

Hi,

in this case IFNULL() is not useful, because count() will return 0 in case the value you submit is NULL, and 1 if you submit a string or a boolean:

select count(null);
+-------------+
| count(null) |
+-------------+
|           0 |
+-------------+
1 row in set (0.00 sec)

-- with IFNULL()
select ifnull(count(null), 123);
+--------------------------+
| ifnull(count(null), 123) |
+--------------------------+
|                        0 |
+--------------------------+
1 row in set (0.00 sec)

So IFNULL can be removed from the query:

"SELECT count(id) AS ctr FROM table WHERE date >= $from_date AND date <= $to_date GROUP BY WEEK(table.date)"

By the way, AND cannot be used with the GROUP BY otherwise you get a syntax error.

it is returning an empty array rather than {0, 0, 0, ... }

I do not understand, are you referring to the result set in the MySQL client or to the result of a (PHP) script?

[ethan.david.376]

SELECT count(id) AS ctr FROM table WHERE date >= $from_date AND date <= $to_date AND GROUP BY WEEK(table.date)

This is working, but the problem is sometimes it return an empty array (array {}) rather than array with 0 values array {0, 0, 0}

[cereal]

This is working, but the problem is sometimes it return an empty array

It can happen if the range in the WHERE statement is not matched, but MySQL does not return arrays, the PHP API can return an array, so if you have problems show the PHP code.

If for some reason you absolutely need to get at least a row with 0, then you can override the empty set by using a variable and applying the IFNULL to the variable. Because if the query returns empty, the variable will be NULL.

Example:

create table D (id int unsigned not null auto_increment primary key, dt datetime not null) engine = myisam;

insert into D (dt) values(now());
insert into D (dt) values(date_add(now(), interval 2 day));
insert into D (dt) values(date_add(now(), interval 3 day));
insert into D (dt) values(date_add(now(), interval 4 day));
insert into D (dt) values(date_add(now(), interval 1 week));
insert into D (dt) values(date_add(now(), interval 2 week));
insert into D (dt) values(date_add(now(), interval 3 week));
insert into D (dt) values(date_sub(now(), interval 1 day));
insert into D (dt) values(date_sub(now(), interval 2 day));
insert into D (dt) values(date_sub(now(), interval 3 day));
insert into D (dt) values(date_sub(now(), interval 1 week));
insert into D (dt) values(date_sub(now(), interval 2 week));
insert into D (dt) values(date_sub(now(), interval 3 week));

-- select
select * from D;
+----+---------------------+
| id | dt                  |
+----+---------------------+
|  1 | 2014-03-11 13:18:50 |
|  2 | 2014-03-12 13:18:50 |
|  3 | 2014-03-13 13:18:50 |
|  4 | 2014-03-16 13:18:50 |
|  5 | 2014-03-23 13:18:50 |
|  6 | 2014-03-30 13:18:50 |
|  7 | 2014-03-08 13:18:50 |
|  8 | 2014-03-07 13:18:50 |
|  9 | 2014-03-06 13:18:50 |
| 10 | 2014-03-02 13:18:50 |
| 11 | 2014-02-23 13:18:50 |
| 12 | 2014-02-16 13:18:50 |
+----+---------------------+
12 rows in set (0.00 sec)

-- select into @tot variable
SELECT @tot:=count(id) FROM D WHERE dt BETWEEN now() AND date_add(now(), interval 1 day) GROUP BY WEEK(D.dt);
Empty set (0.00 sec)

-- show @tot variable
select @tot;
+------+
| @tot |
+------+
| NULL |
+------+
1 row in set (0.00 sec)

-- use IFNULL
select IFNULL(@tot, 0) as ctr;
+-----+
| ctr |
+-----+
|   0 |
+-----+
1 row in set (0.00 sec)

So, if you're using PHP, you can perform multiple queries and return the value of the variable @tot. For example:

$pdo = new PDO('...connection string...', 'user', 'pass');

$pdo->query("SET @tot = NULL");
$stmt = $pdo->prepare("SELECT @tot:=count(id) as total FROM D WHERE dt BETWEEN now() AND date_add(now(), interval 2 week) GROUP BY WEEK(D.dt)");
$stmt->execute();
$rows = $stmt->fetchAll();

$result = $pdo->query("SELECT IFNULL(@tot, 0) as ctr")->fetch(PDO::FETCH_OBJ);

if($result->ctr > 0)
{
    foreach($rows as $row)
    {
        echo '<p>'.$row['total'].'</p>';
    }
}
else
{
    echo 'empty';
}

Or in alternative you can use FOUND_ROWS():

$pdo = new PDO('...connection string...', 'user', 'pass');

$rows = $pdo->query("SELECT count(id) as ctr FROM D WHERE dt BETWEEN now() AND date_add(now(), interval 1 day) GROUP BY WEEK(D.dt)");
$tot = $pdo->query("SELECT FOUND_ROWS() as total")->fetch(PDO::FETCH_OBJ);

if($tot->total > 0)
{
    while($row = $rows->fetch(PDO::FETCH_OBJ))
    {
        echo "<p>".$row->ctr."</p>";
    }
}
else
{
    echo 'empty';
}