MySQLi count(*) always returns 1

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?

Solution 1:

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];

Solution 2:

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.";

Solution 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...