Why Does MySQLi's `count(*)` Return 1 When Counting Table Rows?

MySQLi's count(*) Unexpectedly Returning 1

In counting the number of rows in a MySQL table using MySQLi, you may encounter a strange phenomenon where $result->num_rows consistently reports a count of 1. Let's investigate the correct approach.

The issue lies in how MySQLi handles the result of a COUNT(*) query. Unlike the result of a normal query, which contains a set of rows, COUNT(*) returns a single row with a single column containing the count.

To accurately retrieve this count, you need to fetch the single row from the result set. The following code demonstrates the correct usage:

$result = $db->query("SELECT COUNT(*) FROM `table`");
$row = $result->fetch_row();
$count = $row[0];

echo "Count: $count";

By fetching the first (and only) row and accessing its first column, you obtain the correct count. Using $result->num_rows will always return 1 because it represents the number of rows in the result set, which is 1 for COUNT(*) queries.

Remember, for normal queries where the result contains multiple rows, using $result->num_rows is appropriate. However, for COUNT(*) queries, always follow the pattern above to retrieve the actual count.

The above is the detailed content of Why Does MySQLi's `count(*)` Return 1 When Counting Table Rows?. For more information, please follow other related articles on the PHP Chinese website!