Why does COUNT(*) return 1 in MySQLi when the table has more rows?

Count(*) Function Miscounts in MySQLi

When executing a COUNT(*) query in MySQLi, users may encounter an unexpected behavior where the result consistently returns 1 regardless of the actual table size. This discrepancy between MySQLi and phpMyAdmin can be puzzling.

Investigation and Solution

The mismatch arises because MySQLi represents the COUNT(*) result as a single-row recordset. To access the actual count, the result must be fetched using the fetch_row() method.

Therefore, the correct way to determine the row count using MySQLi is:

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

In this modified code, the num_rows property is not used because the result is a single-row recordset, and thus, its rowCount is always 1. Instead, the fetch_row() method is employed to retrieve the actual count stored in the first column of the recordset.

The above is the detailed content of Why does COUNT(*) return 1 in MySQLi when the table has more rows?. For more information, please follow other related articles on the PHP Chinese website!