English
English
When developing database-related functions using PHP, the mysqli extension provides a complete set of APIs to handle MySQL database operations. Among them, the next_result() function often appears in multi-query scenarios. This article will explore the implementation mechanism of next_result() from the source code perspective, and summarize some efficient use techniques based on practice.
mysqli::next_result() is a method in mysqli extension that is used to switch to the result set of the next statement after multiple statements are executed. This scenario will not appear after calling mysqli::multi_query() .
For example:
$mysqli = new mysqli("localhost", "user", "password", "database");
$sql = "SELECT * FROM users; SELECT * FROM orders;";
if ($mysqli->multi_query($sql)) {
do {
if ($result = $mysqli->store_result()) {
// Processing results
while ($row = $result->fetch_assoc()) {
print_r($row);
}
$result->free();
}
} while ($mysqli->next_result());
}
Next_result() above points the internal result set pointer to the next result set.
In the PHP source code, the implementation of next_result() is located in ext/mysqli/mysqli_nonapi.c , and the underlying MySQL C API function mysql_next_result() is called.
PHP_FUNCTION(mysqli_next_result)
{
MYSQLI_RESOURCE *mysqli_resource;
zval *mysql_link;
if (zend_parse_method_parameters(ZEND_NUM_ARGS(), getThis(), "O", &mysql_link, mysqli_link_class_entry) == FAILURE) {
return;
}
MYSQLI_FETCH_RESOURCE_CONN(mysql, mysql_link, MYSQLI_STATUS_VALID);
RETVAL_BOOL(!mysql_next_result(mysql));
}
The underlying mysql_next_result() will try to get the information of the next result set from the MySQL server and mount it on the current connection object. Its return value is:
0 : Success and there is a result set.
>0 : An error occurred.
-1 : No more result sets.
PHP uses store_result() or use_result() to manage caches for MYSQL_RES type result sets, which is also the reason why the previous result set must be processed before next_result() . Otherwise, the MySQL server will report an error: "Commands out of sync; you can't run this command now".
Prevent missed result sets and cause confusion in connection status. For example:
do {
if ($result = $mysqli->store_result()) {
// Processing results集
$result->free();
}
} while ($mysqli->next_result());
Even if you only care about the first result, use next_result() to process the subsequent results to ensure that the connection is not suspended.
while ($mysqli->more_results()) {
$mysqli->next_result();
}
next_result() returns false not necessarily to indicate an error, it may be that there is no more result sets. It is necessary to combine error or errno to determine whether there is an error.
if (!$mysqli->next_result() && $mysqli->errno) {
// There is an error，Logging
}
Execute multi-statement query after the transaction is enabled to ensure that the result set is processed correctly at each step and avoid transaction commit failures.
When multi-statements are executed using multi_query() , it is easier to introduce SQL injection risks by splicing SQL. Be sure to use parameterization or verification input.
Use mysqli_error() to view the error message.
Enable MySQL general_log or use tools such as tcpdump to capture packets to analyze the multi-result set interaction process.
Try to use mysqli_report() to set detailed error reports in the development environment:
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
Although mysqli::next_result() is just a simple interface call, it hides complex communication process and state management mechanisms behind it. Understanding its underlying logic will help us handle multi-result set scenarios more stably and efficiently. In actual development, following best practices, fully releasing resources, and handling error status are the key to ensuring the stable operation of database interaction.
In background systems involving complex business logic, the rational use of next_result() can not only improve performance, but also avoid many hidden connection state abnormalities, which deserves enough attention.
