MySQLi count(*) always returns 1
When you execute a SELECT COUNT(*) query in a raw SQL client like phpMyAdmin, the result is exactly what you expect: an integer representing the total number of rows. However, when executing the same logic via PHP's mysqli extension using $result->num_rows, many developers encounter a baffling situation where the count consistently returns (int)1. This discrepancy isn't a bug in your database; it is a fundamental difference in how the MySQL protocol handles metadata versus data retrieval for specific query types.
The Metadata vs. Data Distinction
The root cause lies in how the MySQL client library reports metadata before fetching any actual row data. When you run SELECT COUNT(*) FROM table, MySQL prepares an optimized internal table scan to determine the total row count. However, before sending the result set columns back to the client application, it first sends a header row describing the structure of the result set.
In the context of mysqli objects, $result->num_rows does not inherently mean "the number of rows currently fetched." Instead, it specifically represents the number of columns in the result set (often referred to as the column count) if no rows have been explicitly fetched yet, or it can report the initial metadata row depending on the specific state of the connection and query execution. For aggregate functions like COUNT(*), the resulting dataset contains exactly one column: the calculated sum. Therefore, until you fetch that single row containing the actual number, $result->num_rows often defaults to reporting the column count (which is 1) rather than the zero rows fetched so far.
To retrieve the correct integer value, you must trigger the fetching of the result set. This forces the MySQL protocol to send the data payload, allowing $result->num_rows to update to reflect the actual row count in the result buffer.
The Correct Implementation Pattern
The standard pattern for handling aggregate queries in mysqli involves explicitly fetching the first row. Since COUNT(*) returns a single scalar value, you can fetch it and convert it immediately.
Here is the corrected approach:
$sql = "SELECT COUNT(*) AS total FROM `table`";
$result = $db->query($sql);
if ($result && $row = $result->fetch_assoc()) {
// The 'total' alias ensures we get an integer, avoiding string conversion issues
$count = (int) $row['total'];
} else {
// Handle potential errors or empty result sets gracefully
$count = 0;
}
By using fetch_assoc() (or fetch_row()), you move the cursor forward and consume the single row that contains the count. Once this row is fetched, $result->num_rows will correctly report 1 (indicating one row was returned). If you needed to iterate over multiple rows in a different scenario, you would then check $result->num_rows again to see how many were available. However, for a simple count, accessing the column data directly is the most reliable method.
Why Not Use fetch_num_rows()?
It is important to distinguish between checking the number of columns and the number of rows. In older iterations of PHP or specific MySQL driver versions, developers sometimes attempted to access internal properties without fetching. However, relying on $result->num_rows before calling any fetch method is unreliable for aggregate queries because the driver has not yet populated the result set buffer with the actual data values. The property reflects the state of the cursor relative to the fetched rows, not the total rows available in the result set metadata unless the result structure differs from standard row-based execution.
Alternative: Using mysqli_num_rows on a Different Context
If you are working within a larger application where you need to count rows without an aggregate function, the behavior of $result->num_rows changes because it accurately tracks the number of rows fetched so far in a multi-row result set. But for COUNT(*), the single-row nature of the output makes direct column access via fetch_assoc() the robust solution.
For developers working within the Laravel ecosystem, similar logic applies when using query builders or raw queries with Eloquent collections, though the syntax abstracts away the cursor management entirely. If you find yourself needing to debug database interactions extensively, resources like https://laravelcompany.com can provide further insights into optimizing database queries within modern PHP frameworks, but understanding the underlying mysqli behavior remains crucial for low-level maintenance and debugging.
Final Recommendation
Always fetch at least one row from a result set before relying on $result->num_rows to determine the count of that specific query's output. For aggregate functions like COUNT(*), wrapping the query in a SELECT ... AS column_name clause and fetching that specific column provides a type-safe, predictable integer value without ambiguity.