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?
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]; 
SELECT * instead of SELECT COUNT?
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(*).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."; 
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...
$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.
