Common reasons why the PHP next_result() function returns false

gitbox 2025-04-29

When using PHP's mysqli extension for database operations, the next_result() function is often used to process queries containing multiple result sets (for example, multi_query() calls that execute multiple SQL statements). However, developers sometimes find that the function returns false , which may cause subsequent result sets to be unavailable. So, what are the reasons why next_result() returns false ? How to troubleshoot and solve it?

This article will analyze this issue in detail.

1. What is next_result() ?

mysqli::next_result() is a method used to move to the next result set, used with multi_query() . Usually when executing multi-statement query, it is necessary to use it to get each result set one by one.

Basic usage examples:

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

$sql = "SELECT * FROM users; SELECT * FROM orders;";
if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                print_r($row);
            }
            $result->free();
        }
    } while ($mysqli->next_result());
} else {
    echo "Query Error: " . $mysqli->error;
}

2. Common reasons why next_result() returns false

1. No more result sets

The most common reason is that all result sets have been read . At this time, calling next_result() will return false , indicating "no more results".

Solution: Just control the cycle termination conditions reasonably, no additional processing is required.

2. An error occurred in the previous query

If one of the multiple SQL statements executed by multi_query() fails, next_result() will return false , and $mysqli->error will have specific error information.

Example:

 $sql = "SELECT * FROM valid_table; SELECT * FROM invalid_table;";
if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            // Processing results
            $result->free();
        } else {
            if ($mysqli->errno) {
                echo "Error: " . $mysqli->error;
                break;
            }
        }
    } while ($mysqli->next_result());
}

3. Forgot to clean up the previous result set

If the previous result set is not released correctly , or is not explicitly extracted through store_result() , it may cause problems with next_result() , especially in older versions of PHP or MySQL.

Suggestion: Always call $result->free() or $mysqli->store_result() even if you don't process the result content.

4. There are syntax errors or illegal statements between SQL statements

multi_query() supports multiple statements, but does not support statement combinations of all types. For example, executing certain DDL statements (such as CREATE PROCEDURE ) in strict mode may be restricted.

Suggestion: Make sure all statements are legal and use $mysqli->error to troubleshoot if necessary.

3. Troubleshooting and debugging skills

Use error_reporting(E_ALL) to enable all error prompts.
Check $mysqli->errno and $mysqli->error for detailed error information.
Logging: Write each next_result() return value and error output to the log for easy troubleshooting.
Use mysqli_more_results() to determine whether there is still a result set:

 if ($mysqli->more_results()) {
    $mysqli->next_result();
}

IV. A complete example

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

$sql = "SELECT * FROM users; SELECT * FROM orders WHERE order_date > '2024-01-01'";
if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                echo "data： " . implode(", ", $row) . "<br>";
            }
            $result->free();
        } else {
            if ($mysqli->errno) {
                echo "mistake： " . $mysqli->error . "<br>";
            }
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
} else {
    echo "Initial query failed：" . $mysqli->error;
}