English
English
In PHP's MySQLi extension, next_result() is a often overlooked but extremely powerful function. Especially when handling stored procedures and complex multi-query requests, it can help developers to elegantly extract multiple result sets gradually, improving the readability and maintenance of the code.
This article will use a practical case to guide you into the deep understanding of how to use next_result() correctly and efficiently in complex queries, and provide some easy-to-neglect usage techniques.
When executing multiple SQL statements using mysqli::multi_query() , each statement may produce a result set. The next_result() method allows you to jump to the next result set, allowing you to access these results one by one.
Suppose we are developing a backend statistics module and need to obtain the following information through a request:
Total number of users
Number of active users
List of newly registered users in the last week
To improve efficiency, we can package these three queries into a request and send them to the database for processing:
$sql = "
SELECT COUNT(*) AS total_users FROM users;
SELECT COUNT(*) AS active_users FROM users WHERE last_login > NOW() - INTERVAL 30 DAY;
SELECT id, username, created_at FROM users WHERE created_at > NOW() - INTERVAL 7 DAY;
";
Next, we use mysqli::multi_query() to execute this set of queries:
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->connect_error) {
die("Connection failed: " . $mysqli->connect_error);
}
if ($mysqli->multi_query($sql)) {
do {
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_assoc()) {
print_r($row);
}
$result->free();
}
} while ($mysqli->next_result());
} else {
echo "Query failed: " . $mysqli->error;
}
$mysqli->close();
Array
(
[total_users] => 5000
)
Array
(
[active_users] => 1250
)
Array
(
[id] => 1023
[username] => user_2023
[created_at] => 2025-04-22 15:32:00
)
// Other users...
Many developers forget to call next_result() after processing the first result set, resulting in the subsequent result set being ignored or an error occurs.
When multi_query() fails to execute, detailed errors are not thrown like normal queries. Be sure to use $mysqli->error to view the cause of failure.
After each time you get the result set, remember to use $result->free() to free memory, especially when the multi-query result set is large.
Encapsulated result processing logic : Write a dedicated function for the processing of next_result() , unify reading and releasing logic, and improve reusability.
Error handling mechanism integration : the multi_query() error log is uniformly recorded into the log system for easy maintenance.
Result Caching : If the query results can be reused, it can be combined with Redis or file cache to avoid frequent execution of complex queries.
next_result() is an indispensable tool in PHP for processing multi-result sets. By using it rationally, database interaction efficiency can be significantly optimized and system performance can be improved. Whether it is in multi-dimensional statistics or calling multi-step stored procedures, mastering the skills of using next_result() will add a lot of hard power to your PHP development.
If you want to learn more about how to integrate these results to the front-end page, or convert them into a JSON API response, please refer to our next article: How to elegantly output multi-query results to a JSON interface return structure?
