Counting table rows returns 1 row when the table is empty

Question

I want to count the rows where id is let's say 32 with this command:

$countReviews = Yii::$app->db->createCommand("SELECT COUNT(*) 
                                              FROM `product_review`
                                              WHERE `product_review`.`product_id`=$book->id")->execute();

There is no rows in the table but this returns 1 as result. Why is that ? Is this the right way to do the task?Thank you!

Are you sure it returns 1 or some other value ? – Hamza Abdaoui Aug 10 '17 at 10:47 — Hamza Abdaoui, Aug 10 '17 at 10:47

score 3 · Accepted Answer · answered Aug 10 '17 at 10:50

3

An aggregation query with no GROUP BY always returns one row. The value of the count is zero, but the number of rows is 1.

I think you are looking at the number of rows returned by the query, not at the value returned by the count.

answered Aug 10 '17 at 10:50

Gordon Linoff

1,242,037
58
646
786

1

Yes! Made another test and i saw the result which is 0. The array has one result which contains that 0 so i was realy looking at the length of the array i guess. – Toma Tomov Aug 10 '17 at 11:20

score 2 · Answer 2 · answered Aug 10 '17 at 10:48

2

execute() is for sql queries manipulating data (insert/update/delete etc). As documentation says:

This method should only be used for executing non-query SQL statement, such as INSERT, DELETE, UPDATE SQLs. No result set will be returned.

You should use query() here, or even querySacalar() as you're expecting one single integer value.

answered Aug 10 '17 at 10:48

Jakub Matczak

15,341
5
46
64

Thank you! I have to read the documentation one more time i guess. – Toma Tomov Aug 10 '17 at 11:18

Counting table rows returns 1 row when the table is empty

2 Answers2