Why does num_rows return 0? Analysis of common misunderstandings

In PHP, when querying databases using MySQLi extensions, many developers will encounter a confusing question: What is going on? This article will help you sort out common misunderstandings and help you find the root cause of the problem.

Misunderstanding 1: Mysqli_multi_query was used

mysqli_multi_query is a method that can execute multiple SQL statements at once, but it returns a boolean value, and the real result set is obtained through mysqli_store_result or mysqli_use_result .

Sample code:

 <?php
$mysqli = new mysqli("localhost", "user", "password", "database");

$sql = "SELECT * FROM users; SELECT * FROM products;";
if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            echo "Current result set row count: " . $result->num_rows . "<br>";
            $result->free();
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
}
?>

If you try to call num_rows with the multi_query return value, of course you will get 0. Make sure you get the result set object correctly.

Misunderstanding 2: Forgot to execute the query

Some developers wrote $stmt = $mysqli->prepare(...) and then directly used $stmt->num_rows , but note: prepare does not execute queries, it is just preparation.

The query must be executed first and the result must be obtained:

 <?php
$stmt = $mysqli->prepare("SELECT * FROM users WHERE age > ?");
$stmt->bind_param("i", $age);
$age = 18;
$stmt->execute();
$result = $stmt->get_result();
echo "Number of rows: " . $result->num_rows;
?>

If you skip execute or get_result , num_rows has no way to talk about.

Misunderstanding 3: UNBUFFERED query was used

By default, MySQLi uses buffered queries, and you can use num_rows directly. However, if using MYSQLI_USE_RESULT mode, the result set is streaming, and MySQL cannot calculate the total number of rows before it is traversed.

Example:

 <?php
$result = $mysqli->query("SELECT * FROM users", MYSQLI_USE_RESULT);
echo $result->num_rows; // mistake：此时无法获取Number of rows

The solution is to use the default buffering mode or iterate through the statistics yourself.

Misunderstanding 4: Misunderstanding the meaning of num_rows

Many people mistakenly believe that num_rows can be used in INSERT , UPDATE , and DELETE queries. In fact, these operations have no result set, so you should use $mysqli->affected_rows .

 <?php
$mysqli->query("UPDATE users SET status = 'active' WHERE last_login > NOW() - INTERVAL 30 DAY");
echo "受影响的Number of rows: " . $mysqli->affected_rows;
?>

Remember: only the result set produced by the SELECT query has num_rows .

summary

If you encounter num_rows always returns 0, check first:

? Did you use multi_query but failed to get the result set?
? Did you forget to execute and get_result after prepare ?
? Did you use UNBUFFERED mode?
? Did you get the query type wrong?

By avoiding these misunderstandings, you can use mysqli_result and num_rows correctly and avoid detours.