English
English
During the interaction between PHP and MySQL, developers often come into contact with the functions multi_query() and next_result() . However, many beginners tend to confuse the uses of these two functions when reading documents or trying to develop them, and even mistakenly believe that next_result() can be used to execute multiple SQL statements alone.
This article will explore in-depth the responsibilities, working mechanisms, usage of these two functions in PHP, and why next_result() cannot replace multi_query() .
multi_query() is a function in the PHP mysqli extension. Its function is to execute multiple SQL statements at once . These SQL statements must be separated by semicolons ( ; ) to form a long SQL string.
$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()) {
while ($row = $result->fetch_assoc()) {
print_r($row);
}
$result->free();
}
} while ($mysqli->next_result());
} else {
echo "Execution failed: " . $mysqli->error;
}
In this example, multi_query() starts a multi-result set processing flow, and then accesses the results of each statement in turn through next_result() .
The responsibility of next_result() is to push mysqli to the next result set . It does not execute any SQL, but just moves from the result that has been executed by multi_query() to the return result of the next statement.
That is, next_result() itself does not execute SQL, it just traversal .
Without the pre-call call of multi_query() , next_result() has nothing to do. The context it depends on is "multiple statements have been executed".
It's like asking "Why can't you turn on the TV with a remote control without plugging in the power supply?" because next_result() is part of the process flow, not the starting point for the execution of the action.
Different responsibilities
multi_query() is the executor.
next_result() is an iterator.
Dependencies
Without the context of multi_query() , calling next_result() will return false .
next_result() only makes sense after multi_query() is successfully executed.
Error handling and state management
multi_query() is responsible for executing and initializing all statements. If an error occurs, it can be interrupted immediately.
next_result() is the result set that does not retry or reexecute SQL.
Scenario: Execute multiple initialization statements, such as table creation and data insertion
$sql = "
CREATE TABLE IF NOT EXISTS logs (id INT AUTO_INCREMENT PRIMARY KEY, message TEXT);
INSERT INTO logs (message) VALUES ('Initialization successfully');
SELECT * FROM logs;
";
$mysqli = new mysqli("localhost", "user", "password", "database");
if ($mysqli->multi_query($sql)) {
do {
if ($result = $mysqli->store_result()) {
while ($row = $result->fetch_assoc()) {
echo $row['message'] . "\n";
}
$result->free();
}
} while ($mysqli->next_result());
} else {
echo "An execution error: " . $mysqli->error;
}
You can apply this application in scenarios such as deployment scripts, batch initialization of data, etc.
In PHP database programming, multi_query() and next_result() are a pair of tools used together. The former is responsible for sending multiple statements to the database for execution, while the latter is responsible for fetching the execution results of each statement.
Trying to use next_result() to execute SQL statements is equivalent to "only turn on the TV with the remote control but not plugged in", it cannot complete the task independently. Only by fully understanding their respective responsibilities and contexts can efficient and stable database interaction code be written.
If you are still using the REST interface to test multi-statement execution, such as submitting multiple statements to https://gitbox.net/api/sql-exec , you should also remember to use a multi-statement execution method to simulate the effect of multi_query() .
