MySQLi count(*) always returns 1

Question

I'm trying to count the number of rows in a table and thought that this was the correct way to do that:

$result = $db->query("SELECT COUNT(*) FROM `table`;");
$count = $result->num_rows;

But counts always returns (int)1. If I use the same query in phpMyAdmin I get the right result. It sits in a table so I tried testing $count[0] as well, but that returns NULL.

What is the right way to do this?

score 107 · Accepted Answer · answered Aug 31 '10 at 20:44

107

You have to fetch that one record, it will contain the result of Count()

$result = $db->query("SELECT COUNT(*) FROM `table`");
$row = $result->fetch_row();
echo '#: ', $row[0];

answered Aug 31 '10 at 20:44

VolkerK

95,432
20
163
226

Thanks! So num_rows only should be used for `SELECT *` instead of `SELECT COUNT`? – Lode Sep 01 '10 at 06:34
4

More or les, yes. `num_rows` can tell you how many result records _have been transferred_ to the client. If that's the number you want to know num_rows is what you should use. If you want to know how many records there are in the database (without transferring the data to the client) use Count(*). – VolkerK Sep 01 '10 at 09:15

score 12 · Answer 2 · edited Feb 01 '21 at 18:18

12

Always try to do an associative fetch, that way you can easy get what you want in multiple case result

Here's an example

$result = $mysqli->query("SELECT COUNT(*) AS cityCount FROM myCity")
$row = $result->fetch_assoc();
echo $row['cityCount']." rows in table myCity.";

edited Feb 01 '21 at 18:18

Dharman

30,962
25
85
135

answered Aug 31 '10 at 20:46

ErVeY

1,524
4
16
26

Curious what do you mean by multiple case result? A select with multiple counts using case statements? – Mike Causer Aug 02 '12 at 14:47

score 3 · Answer 3 · edited Jan 18 '20 at 19:38

3

I find this way more readable:

$result = $mysqli->query('select count(*) as `c` from `table`');
$count = $result->fetch_object()->c;
echo "there are {$count} rows in the table";

Not that I have anything against arrays...

edited Jan 18 '20 at 19:38

Dharman

30,962
25
85
135

answered Aug 02 '12 at 14:58

Mike Causer

8,196
2
43
63

score 0 · Answer 4 · answered Nov 07 '11 at 20:50

0

$result->num_rows; only returns the number of row(s) affected by a query. When you are performing a count(*) on a table it only returns one row so you can not have an other result than 1.

answered Nov 07 '11 at 20:50

Stephane Paquet

2,315
27
31

4

Incorrect. mysqli_result::$num_rows returns the row count of a result set. mysqli::$affected_rows returns the number of rows affected by a query. – Aug 08 '13 at 06:16

MySQLi count(*) always returns 1

4 Answers4

Linked

Related