How to get the result set using use_result() before next_result()

gitbox 2025-04-29

When using PHP to operate MySQL multi-statement query, multi_query() is often used to traverse multiple result sets with next_result() . Before calling next_result() , correctly using use_result() is a key step to ensure that the result is not missed or error-handled. This article will introduce its correct usage and give a complete code example.

Why use_result() is needed

After performing a multi-statement query, MySQL returns multiple result sets. PHP provides two ways to get the result set: store_result() and use_result() . Among them, use_result() is a non-buffered method and is suitable for reading the results of large data volumes, which will immediately start reading data from the server.

If you call next_result() before processing the current result set, the current result set will be discarded by MySQL. This will cause you to be unable to access those results and may also throw an error. Therefore, use_result() must be called first to get the current result set and then read it.

Correct usage

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

if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

$sql = "SELECT * FROM users; SELECT * FROM products;";
if ($mysqli->multi_query($sql)) {
    do {
        // Calling next_result() The current result set must be obtained before
        if ($result = $mysqli->use_result()) {
            while ($row = $result->fetch_assoc()) {
                print_r($row);
            }
            $result->free(); // Remember to release the result set resource
        } else {
            echo "There is currently no result set or an error：" . $mysqli->error;
        }
    } while ($mysqli->next_result());
} else {
    echo "Query failed：" . $mysqli->error;
}

$mysqli->close();
?>

Things to note

Call use_result() only once : Use_result() can only be used once per result set, otherwise an error will be reported.
Release resources : After processing the results, be sure to call $result->free() to free up memory.
Error checking : use_result() may return false and should be judged.

Conclusion

Mastering the correct order of use_result() and next_result() will not only help you avoid common logical errors when handling multi-statement queries, but also improve the robustness of your program. If you need to call multiple SQL queries in your project and the result set is large, it is recommended to give priority to use_result() and strictly abide by the above process.

For more practical skills, you can visit our website: https://gitbox.net/php-tips

next_result