Count(id) mysql - show 0 if no results?

Question

I want to count(id) if an user has new chat messages.

My problem is if there is no message it doesn't give me 0 as a result. What is wrong?

$stmt = $mysqli->prepare("SELECT count(id) FROM chat WHERE `to`=? and recd='0' group by `from`");
$stmt->bind_param('s', $user);
$stmt->execute();
$stmt->bind_result($chatcont);
$stmt->fetch();
$stmt->close();

php:

echo "($chatcont)";

if no message I have:

()

I want to show 0 inside this.

I believe that `NULL` output is coming instead of `0` in the count because of the `GROUP BY` command. When there are `0 rows` in the output, the GROUP BY command cannot GROUP anything, thus producing a NULL structure. — Anshuman Chatterjee, Sep 18 '15 at 12:27
oh, you are right! group by is the problem here... should I use num rows instead? because I need to count messages by senders, not by the real number of messages... — RGS, Sep 18 '15 at 12:32

score 2 · Answer 1 · answered Sep 18 '15 at 12:23

2

Use $stmt->num_rows to return the number of rows in your result. If that is 0 then echo 0 else echo $chatcont

answered Sep 18 '15 at 12:23

AgeDeO

3,137
2
25
57

thank you! the problem in count is group by as Anshuman Chatterjee said! I think the solution is num_rows indeed... thanks – RGS Sep 18 '15 at 12:34

score 0 · Answer 2 · answered Sep 18 '15 at 12:33

0

Yet you don't need to use bind_result in PDO statements. Please, avoid unnecessary actions. Just get your data and use it anywhere.

$stmt = $mysqli->prepare("SELECT count(id) as count FROM chat WHERE `to`=? and recd='0' group by `from`");
$stmt->execute(array($user));
$chatcont = $stmt->fetch();

echo $chatcont['count'];

Here, you will get either count or 0

answered Sep 18 '15 at 12:33

Bhanu Chandar

33
5

there is no PDO around – Your Common Sense Sep 18 '15 at 13:15

score 0 · Accepted Answer · edited May 23 '17 at 11:44

That's one of questions that, being wrongly put, make people writing wrong answers all around.

Using SQL properly, count() will always return a number.

It's SQL which is wrong. While accepted answer is essentially wrong and should never be used, as it has been answered here thousands times already, all in vain.

SELECT count(id) FROM chat WHERE `to`=? and recd='0'

is the right answer to get the number of unread messages.

To get the number of senders is another question. That can could have been answered with SQL as well. If were ever asked.

SELECT count(distinct `from`) FROM chat WHERE `to`=? and recd='0'

@RickJoe this one looks like the only correct answer to me. Why you need to use group by? — fthiella, Sep 18 '15 at 13:13

Count(id) mysql - show 0 if no results?

3 Answers3