English
English
When using PHP and MySQLi for database operations, we usually only execute one query at a time. However, in some complex business scenarios, multiple query statements may need to be executed through one request, and at this time multiple result sets need to be processed. PHP's mysqli extension provides the next_result() function to help us traverse these result sets.
Multiple result sets usually occur when SQL queries containing multiple statements are executed, for example:
SELECT * FROM users; SELECT * FROM orders;
This is common when SQL is executed in stored procedures (Stored Procedures) or in batches. Mysqli_multi_query() in PHP supports this form of batch execution.
The basic steps for using mysqli_multi_query() and next_result() to process multiple result sets in PHP are as follows:
Create a database connection
Use mysqli_multi_query() to perform multi-statement query
Use store_result() to get the current result set
Use next_result() to move to the next result set
Here is a concrete example showing how to iterate through multiple result sets and extract data:
<?php
$mysqli = new mysqli("localhost", "username", "password", "database");
if ($mysqli->connect_errno) {
die("Connection failed: " . $mysqli->connect_error);
}
$sql = "SELECT id, username FROM users; SELECT id, order_date FROM orders;";
if ($mysqli->multi_query($sql)) {
do {
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_assoc()) {
echo "<pre>";
print_r($row);
echo "</pre>";
}
$result->free();
}
// Check if there are more result sets
if ($mysqli->more_results()) {
echo "<hr>Next result set：<br>";
}
} while ($mysqli->next_result());
} else {
echo "Query execution failed: " . $mysqli->error;
}
$mysqli->close();
?>
Ensure that SQL statements are separated by semicolons : This is necessary for multi_query() .
Each result set must be extracted using store_result() and resources must be released in time .
next_result() does not return data, its function is to enable store_result() to extract the next result set .
During debugging, you can print mysqli->error to view error information .
Read data from multiple tables in batches and display them on the same page
Call stored procedures that return multiple result sets
When performing complex statistical analysis SQL queries
When executing multi-statement queries, be sure to pay attention to SQL injection risks. Using prepare and bind_param methods is a safer approach, although multi_query() itself does not support prepare , it can split the logic into multiple single queries to avoid risks.
