How to use the next_result() function to efficiently process multiple result sets and implement complex database batch processing?

gitbox 2025-05-06

When processing database operations in PHP, sometimes we encounter situations where we need to process multiple query result sets at once. Traditional single query result sets are relatively simple to handle, but when it comes to the return of multiple queries, how to efficiently handle these result sets becomes a challenge. Fortunately, MySQL's next_result() function provides us with an elegant solution, which can significantly improve efficiency especially when performing complex database batching.

What is next_result() ?

next_result() is a function provided by the MySQLi extension to get the next result of the current query result set. Suppose we execute multiple query statements at once, next_result() allows us to process the result sets of these queries one by one.

When executing multiple SQL queries in PHP, the multi_query() method is usually used, and next_result() is a tool that cooperates with multi_query() to traverse multiple query result sets.

Basic usage of next_result()

1. Execute multiple queries

First, we need to use the MySQLi extension multi_query() to execute multiple queries. The key here is that multi_query() will submit multiple SQL statements to the database at one time, and the database will return multiple result sets.

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

// Check if the connection is successful
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Execute multiple query statements
$sql = "SELECT * FROM users; SELECT * FROM orders; SELECT * FROM products;";
if ($mysqli->multi_query($sql)) {
    do {
        // Get the result set of the current query
        if ($result = $mysqli->store_result()) {
            // Processing result sets
            while ($row = $result->fetch_assoc()) {
                echo "User ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
            }
            // Release the current result set
            $result->free();
        }
        // Get the next result set
    } while ($mysqli->next_result());
}

// Close the connection
$mysqli->close();
?>

2. Explain the code

multi_query() : This function will send multiple SQL query statements to the database together. It returns true if execution is successful.
do-while loop : This is used to iterate through all returned result sets. After each call next_result() , the store_result() function returns the next result set.
next_result() : used to switch between multiple result sets to ensure that we can handle the result set of each query.

3. Efficiently handle complex database batch processing

In practical applications, you may encounter situations where you need to process a large amount of data. For example, batch updates of databases or computes large-scale statistics. If multiple queries are used with multi_query() and next_result() to execute multiple queries, it can significantly improve execution efficiency and reduce the time of requests.

Here is a more complex example that demonstrates how to execute multiple queries in a batch operation and perform different processing based on the returned data.

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

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

$sql = "
    SELECT id, name FROM users;
    SELECT COUNT(*) AS order_count FROM orders WHERE user_id = 1;
    SELECT * FROM products WHERE price > 100;
";

if ($mysqli->multi_query($sql)) {
    do {
        // Process user query results
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                echo "User ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
            }
            $result->free();
        }
        // Processing order quantity query results
        if ($result = $mysqli->store_result()) {
            $row = $result->fetch_assoc();
            echo "Total Orders: " . $row["order_count"] . "<br>";
            $result->free();
        }
        // Process product query results
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                echo "Product: " . $row["name"] . " - Price: " . $row["price"] . "<br>";
            }
            $result->free();
        }
    } while ($mysqli->next_result());
}

$mysqli->close();
?>

4. Why can use next_result() improve efficiency?

In the absence of next_result() , usually every time a query is executed, you need to wait for the result to return before performing the next operation. And through multi_query() and next_result() , we can send multiple query requests at once and process the returned result set one by one. This approach can reduce the overhead of database connections and the processing is continuous, thereby significantly improving the efficiency of batch processing.

Through the above examples and explanations, we can see that using the next_result() function to efficiently process multi-result sets in PHP is a very powerful and practical way to perform complex database batch processing. It not only helps us reduce the number of database interactions, but also improves query efficiency, allowing applications to perform better when processing large amounts of data.

next_result