Use next_result() to handle stored procedures with cursors

In PHP, handling stored procedures with cursors is a common operation, especially when interacting with a database. Cursors are a control structure in a database that allows developers to retrieve data line by line. When you use a MySQL or MariaDB database, stored procedures can return multiple result sets, and the next_result() function is used to handle this situation.

1. Basic concepts

A stored procedure is a precompiled collection of SQL statements that are stored in a database and can be called multiple times. Stored procedures can return multiple result sets, often processing these result sets one by one through cursors. In PHP, you can interact with a MySQL database through mysqli extension or PDO extension.

When a stored procedure returns multiple result sets, we can use next_result() to retrieve all result sets step by step. This function is very suitable for handling complex operations like paging queries that return multiple result sets.

2. Create a stored procedure with a cursor

First, we need to create a stored procedure that returns multiple result sets. In the database, you can create a simple stored procedure that simulates returning multiple query results. Here is an example of creating a stored procedure:

 DELIMITER $$

CREATE PROCEDURE multiple_results()
BEGIN
    -- Return the first result set
    SELECT * FROM users;

    -- Return the second result set
    SELECT * FROM orders;
END$$

DELIMITER ;

This stored procedure multiple_results() returns the result sets of two queries: the data of the users table and the data of the orders table.

3. Use PHP's next_result() function to handle cursors

Next, we will write PHP code to call this stored procedure and use the next_result() function to get all the result sets one by one.

 <?php
// create MySQLi connect
$mysqli = new mysqli("localhost", "username", "password", "database_name");

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

// Call stored procedures
$mysqli->multi_query("CALL multiple_results()");

// Looping the result set
do {
    // Get the current result set
    if ($result = $mysqli->store_result()) {
        while ($row = $result->fetch_assoc()) {
            // Output the content of the current result set
            echo "User ID: " . $row['id'] . " - Name: " . $row['name'] . "<br>";
        }
        $result->free();
    }
    // Get the next result set
} while ($mysqli->next_result());

// 关闭connect
$mysqli->close();
?>

4. Code parsing

• Create a connection : First, we create a connection to the MySQL database through new mysqli() . Please modify the host, user name, password and database name of the database according to the actual situation.

• Execute stored procedures : Execute stored procedures through the multi_query() method. In this example, CALL multiple_results() executes the created stored procedure.

• Process multiple result sets : Use a do-while loop to get all result sets one by one. In each loop, we get the current result set through the store_result() method and process it. If there are multiple result sets, next_result() will jump to the next result set.

• Free result set : After processing the current result set, free memory through $result->free() .

5. Things to note

• Error handling : In actual applications, make sure to add appropriate error handling mechanisms to database operations, such as checking whether stored procedures are executed successfully.

• Processing of multiple result sets : If the stored procedure returns a very large result set or the amount of query data is large, it may cause performance problems. You can consider paging queries or optimizing queries.

6. Sample output

Suppose that the users table and the orders table contain the following data respectively:

users table:

 +----+-------+
| id | name  |
+----+-------+
| 1  | John  |
| 2  | Jane  |
+----+-------+

Orders table:

 +----+------------+
| id | order_name |
+----+------------+
| 1  | Order A    |
| 2  | Order B    |
+----+------------+

The output will be:

 User ID: 1 - Name: John
User ID: 2 - Name: Jane
Order ID: 1 - Order Name: Order A
Order ID: 2 - Order Name: Order B

7. Summary

Use PHP's next_result() function to efficiently handle stored procedures with multiple result sets. Through appropriate database queries and cursor processing, we can gradually obtain each result set and flexibly process the data in the application. This technique is very useful when developing complex database applications, especially in scenarios where batch operations or paging queries are required.