English
English
When using PHP for database operations, it is sometimes necessary to execute multiple SQL statements at a time to improve efficiency. The MySQLi extension provides support for multi-statement execution, but when processing the results of multiple statements, you need to use next_result() to correctly traverse each result set. This article will introduce in detail the role and usage scenarios of next_result() .
Multi-statement execution allows multiple SQL statements to be executed in a database request, separating each statement with a semicolon ( ; ). For example:
SELECT * FROM users;
SELECT * FROM orders;
By using the mysqli::multi_query() method, the above multiple queries can be executed at one time, thereby reducing the number of interactions with the database and improving performance.
When executing multiple statements using multi_query() , each statement returns an independent result set. The purpose of next_result() is to iterate through these result sets to ensure that each statement can be processed correctly.
If next_result() is not called, the results of the first statement can only be processed, and the results of the remaining statements will be ignored or result in an error.
Here is a complete example using multi_query() and next_result() :
<?php
// Database connection configuration
$host = 'localhost';
$user = 'root';
$password = 'password';
$database = 'test_db';
// Create a connection
$mysqli = new mysqli($host, $user, $password, $database);
// Check the connection
if ($mysqli->connect_error) {
die('Connection failed：' . $mysqli->connect_error);
}
// Multiple SQL Statement
$sql = "SELECT * FROM users; SELECT * FROM orders;";
// 执行多Statement查询
if ($mysqli->multi_query($sql)) {
do {
// Store the current result set
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_assoc()) {
print_r($row);
}
// Release the result set
$result->free();
}
// Check if there are more result sets
} while ($mysqli->more_results() && $mysqli->next_result());
} else {
echo "Query failed：" . $mysqli->error;
}
// Close the connection
$mysqli->close();
?>
Establish a database connection:
Use mysqli to connect to the database.
Prepare multiple SQL statements:
Separate multiple query statements with semicolons.
Execute multi-statement query:
Use multi_query() to execute all statements.
Traversing the result set:
Get the current result set through store_result() .
Use fetch_assoc() to output data line by line.
Free the current result set to save memory.
Use next_result() to move to the next result set until all results are processed.
Close the database connection:
Free up the resource and close the connection.
Make sure to use the object-oriented mode extended by mysqli .
Each SQL statement needs to be separated by a semicolon ( ; ), otherwise it may lead to syntax errors.
When processing results, be sure to use next_result() to iterate through all result sets, otherwise a "Commands out of sync" error may occur.
next_result() is commonly used in the following scenarios:
Batch query of data from multiple tables.
Execute multiple update, insert or delete statements in batches.
When executing a stored procedure returns multiple result sets.
For example, when batch querying user information and order data, two result sets can be obtained with just one database connection, simplifying logic and improving efficiency.
next_result() is an indispensable part of handling multi-statement query, and it ensures that each result set can be processed correctly. By rationally using multi_query() and next_result() , the efficiency of database operations can be effectively improved, especially when batch processing of data is required.
