Use next_result() to correctly process multiple results returned by stored procedures

gitbox 2025-05-02

When developing PHP applications, you often encounter situations where stored procedures need to be executed, especially when stored procedures return multiple result sets. How to correctly handle these multiple result sets in PHP is a very important question. The MySQLi extension for PHP provides a method called next_result() that can help us achieve this goal.

1. Stored procedure returns backgrounds for multiple result sets

When we execute a stored procedure in the database, multiple result sets may be returned. This is useful when dealing with complex queries, such as getting multiple different data sets in a single database operation. Suppose our stored procedure returns three result sets of user's basic information, user's order history, and user's payment records. We need to process these result sets one by one.

2. Execute stored procedures using MySQLi

First, we need to execute stored procedures with MySQLi. Here is a basic PHP example that demonstrates how to call a stored procedure and process multiple result sets returned:

 <?php
// Connect to the database
$mysqli = new mysqli("localhost", "username", "password", "database_name");

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

// Execute stored procedures
$query = "CALL your_stored_procedure()";
if ($result = $mysqli->query($query)) {
    // Process the first result set
    if ($result->num_rows > 0) {
        while ($row = $result->fetch_assoc()) {
            echo "userID: " . $row["user_id"] . " - Name: " . $row["name"] . "<br>";
        }
    }
    
    // Get the next result set
    if ($mysqli->more_results()) {
        $mysqli->next_result();
        // Processing the second result set
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                echo "OrderID: " . $row["order_id"] . " - Amount: " . $row["amount"] . "<br>";
            }
        }
    }
    
    // Get the next result set
    if ($mysqli->more_results()) {
        $mysqli->next_result();
        // Processing the third result set
        if ($result = $mysqli->store_result()) {
            while ($row = $result->fetch_assoc()) {
                echo "PayID: " . $row["payment_id"] . " - state: " . $row["status"] . "<br>";
            }
        }
    }
} else {
    echo "mistake: " . $mysqli->error;
}

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

3. Code parsing

CALL your_stored_procedure() : Execute stored procedures.
$mysqli->query($query) : Send query to the database.
more_results() : Check if more result sets are available.
next_result() : Get the next result set. In PHP, you need to call next_result() to move correctly to the next result set.
store_result() : Extract data from the current result set.

4. Processing key points of multiple result sets

There are several key points to note when working with multiple result sets:

more_results() : Each time you query multiple result sets, you must first check whether there are more result sets. When there are no more result sets, more_results() returns false .
next_result() : After each execution of next_result() , MySQLi will automatically jump to the next result set. At this time, you can use store_result() to get and process the new result set.
Avoid result set conflicts : Make sure that after processing one result set, call next_result() to correctly jump to the next result set to avoid data confusion.

5. FAQs and Solutions

Error Handling : Ensure error checking is performed before and after each query is executed. If a step fails, the error message can be obtained through mysqli_error($mysqli) .
Performance Optimization : Make sure your query and stored procedure design is efficient when processing multiple result sets. If there are multiple complex queries, you can consider splitting them into multiple separate stored procedures to reduce the complexity of one operation.

next_result