English
English
In PHP, when we use MySQLi extensions for database operations, we may encounter a situation related to multiple result sets. Especially when executing stored procedures or multiple query statements, next_result() and store_result() are two commonly used functions. In this article, we will explore how these two functions work together to ensure that multiple query results can be processed correctly.
store_result() : This function is used to store query results in memory. It reads all data at once and saves it in the mysqli_result object, suitable for scenarios where query results need to be reused in multiple places.
next_result() : This function is used to process multiple query statements. When multiple queries are executed, it is used to point to the next result set. If multiple result sets exist, calling next_result() allows you to iterate through the results of each query.
When you execute a SQL statement containing multiple queries (for example, multiple SELECT queries, or multiple result sets in stored procedures), you must ensure that each query result is read and processed correctly. In this case, store_result() is used to store the results of the current query, while next_result() is used to point to the next result set.
Suppose we have a database, and the stored procedure getMultipleResults() returns multiple query results. To ensure that each result set can be read and processed, we need to use store_result() to store the current result first, and then use next_result() to get the next result set.
<?php
// Create a connection
$mysqli = new mysqli("localhost", "user", "password", "database");
// Check the connection
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
// Execute a query
$query = "CALL getMultipleResults()";
if ($mysqli->multi_query($query)) {
do {
// Store the current result set
if ($result = $mysqli->store_result()) {
// Processing result sets
while ($row = $result->fetch_assoc()) {
echo "ID: " . $row['id'] . " Name: " . $row['name'] . "<br>";
}
$result->free();
}
// Get the next result set
} while ($mysqli->next_result());
} else {
echo "Query failed: " . $mysqli->error;
}
// Close the connection
$mysqli->close();
?>
In the above code:
A SQL statement containing multiple queries is executed using multi_query() .
Use a do...while loop to process each query result set:
store_result() stores the current query result in memory.
Get and display each row of data through the fetch_assoc() method.
Use next_result() to get the result set of the next query until there are no more results.
Memory usage : If the query result is large, frequent use of store_result() may take up more memory. At this time, you can consider using use_result() , which can read query results line by line to reduce memory usage.
Query order : When executing multiple queries with multi_query() , make sure that each query returns the result set correctly, otherwise next_result() may not be executed correctly.
Error handling : Be sure to check the execution status of each query to ensure that no errors occur and avoid program crashes due to query failure.
By combining next_result() and store_result() , you can handle multiple result sets in PHP, ensuring that each query result is read and displayed correctly. This is very useful for situations involving stored procedures or multiple queries, and can greatly improve the flexibility and stability of the code.
