Decoding num_rows and fetch_assoc in MySQLi Prepared Statements
The confusion surrounding mysqli_stmt::num_rows often stems from a fundamental misunderstanding of how MySQLi handles result sets compared to older, procedural styles or PDO. In the code snippet you are reviewing, the logic appears sound at a glance, but the interaction between bind_param, execute, and store_result is where the complexity lies.
When you execute a prepared statement in MySQLi, the database driver sends the query to the server and retrieves the result set metadata. However, accessing row counts requires a specific sequence of method calls that ensures the result buffer is fully populated before attempting to read properties like num_rows.
In your example, calling $stmt->store_result() immediately after $stmt->execute() is the critical step. Without this call, the result set might not be fetched into the client-side buffer yet. If you attempt to access $stmt->num_rows or iterate over results before storing the result, you risk encountering a situation where the row count is reported as zero or null, even if data exists on the server. The store_result() method forces the driver to download the complete result set from the MySQL server into memory (or a buffer), making subsequent property access reliable.
Once store_result() has been called, $stmt->num_rows will return the total number of rows in the result set. This is distinct from $result->num_rows, which you would use if you were using the older procedural mysqli_query style where the query returns a resource directly rather than a statement object. In modern MySQLi, it is best practice to check the row count via the statement object itself after binding parameters and executing.
Regarding the collection of data, $stmt->fetch_assoc() retrieves a single row as an associative array. The loop in your code:
while($data = $result->fetch_assoc()) {
// Loop through results here $data[]
}
uses the result resource stored in $result to fetch rows one by one. While this works, it is technically redundant because the statement object $stmt also possesses a fetch_assoc() method. Using $result->fetch_assoc() is acceptable if you have explicitly assigned the result to a separate variable, but relying solely on $stmt simplifies the code structure. You can iterate directly using:
while ($data = $stmt->fetch_assoc()) {
// Process $data here
}
This approach avoids creating an intermediate $result variable that is often unnecessary when working with prepared statements. It keeps the scope cleaner and reduces the chance of confusion regarding which object holds the cursor position.
A common pitfall in this scenario involves checking num_rows >= "1". While functionally correct, comparing a numeric property to a string literal can lead to type coercion warnings in strict PHP environments. It is more robust to compare against an integer: $stmt->num_rows > 0. This ensures that the check fails if no rows are found without triggering potential type mismatch issues. Furthermore, remember that num_rows returns the total number of rows, not just the ones fetched so far. If you were to fetch all rows into an array first and then try to get the count from count($array), you would achieve the same result but with higher memory overhead for large datasets.
If your application logic requires processing a massive dataset where loading all rows into memory is not feasible, you might consider using a cursor-based approach or streaming results directly without pre-checking num_rows. However, for typical use cases like checking license validity counts, the pattern demonstratedâexecuting, storing the result, checking the count, and then iteratingâis efficient and standard.
For developers looking to integrate this logic into larger frameworks, Laravel simplifies these interactions through its database facade, abstracting away the low-level store_result() calls. While the underlying mechanics remain consistent with raw MySQLi, understanding these details ensures you can write performant code whether you are using a framework or vanilla PHP. You can explore more about handling database operations in modern contexts at https://laravelcompany.com.