Analysis of the reasons why next_result() causes a dead loop when used in a loop

gitbox 2025-04-29

Developers often use the next_result() function when using PHP for database operations, especially when using MySQLi's multi-result set (multi_query) function. However, if used improperly in the loop, it may trigger, causing the script to fail to end normally. This article will analyze the reasons for this occurrence in detail and provide a reliable solution.

1. Problem reappears

Suppose we have a piece of code that uses multi_query to execute multiple SQL statements:

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

$sql = "
    SELECT * FROM users;
    SELECT * FROM orders;
    SELECT * FROM products;
";

if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                print_r($row);
            }
            $result->free();
        }
        // Here is the key
    } while ($mysqli->next_result());
}

In this code, we want to execute three SELECT statements in sequence and process their respective result sets. The purpose of next_result() is to prepare the next result set.

The problem is: if the last time next_result() is called is true , but store_result() returns false (because there are no more result sets), it will enter a dead loop.

2. Why is there a vicious cycle?

The purpose of next_result() is to move mysqli to the next result set and return a boolean:

Returning true means there are more result sets, or a successful switch;
Returning false means there are no more result sets or an error occurs.

However, when the last result set is an empty result set (such as the last statement is SELECT but no result, or a non-query statement such as UPDATE ), store_result() returns false , but next_result() may still return true , causing do...while to continue execution - there is no exit condition, and the dead loop occurs!

3. Solution

? Method 1: Check if there are more result sets

You can use the more_results() method to determine whether there is still a result set that can be processed:

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

$sql = "
    SELECT * FROM users;
    SELECT * FROM orders;
    UPDATE users SET status = 'active' WHERE id = 1;
";

if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                print_r($row);
            }
            $result->free();
        } else {
            // Not a result set，may be UPDATE、INSERT wait
            if ($mysqli->errno) {
                echo "MySQL mistake：" . $mysqli->error;
            }
        }

    } while ($mysqli->more_results() && $mysqli->next_result());
}

? Method 2: Set the maximum number of cycles (defensive)

If you cannot fully determine the number of result sets, you can set a maximum number of loops to prevent dead loops:

 $maxLoops = 10;
$loopCounter = 0;

if ($mysqli->multi_query($sql)) {
    do {
        if (++$loopCounter > $maxLoops) {
            echo "A possible dead loop was detected，Abort the operation";
            break;
        }

        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                print_r($row);
            }
            $result->free();
        }

    } while ($mysqli->next_result());
}

4. Summary

When using next_result() , you must pay special attention to the return value that cannot be fully trusted. To use with more_results() , make sure to continue only if there is indeed the next result set. Otherwise, it is easy to cause a dead loop under certain SQL execution paths, especially when there are empty result sets or mixed execution of non-query statements.

To see more best practices about PHP and database operations, you can visit:

https://gitbox.net/php-mysql-best-practices

next_result