Execute multiple SQL commands at once using the Multi_query () method in the Mysqli object.
Specific practices:
Write multiple SQL commands in the same string as arguments to the Multi_query () method, separating multiple SQL with semicolons (;). If the first command does not go wrong in execution, this method returns True, otherwise false.
Because the Multi_query () method is capable of connecting to execute one or more queries, each SQL command may return a result and need to obtain each result set if necessary. So there are some changes to the processing of the return result of the method, the result of the first query command is read with the Use_result () or Store_result () method in the Mysqli object, and of course, the full result is returned to the client immediately using the Store_result () method, This approach is more efficient. In addition, you can use the More_results () method in the Mysqli object to check if there are other result sets.
If you want to process the next result set, you should call the Next_results () method in the Mysqli object to get the next result set. This method returns TRUE or false.
If you have the next result set, you also need to use the Use_result () or Store_result () method to read.
Execute multiple SQL command codes as follows:
<?php
$mysqli =new mysqli ("localhost", "Mysql_user", "Mysql_pwd", "my_db_name");
/* Check connection */
if ($mysqli->mysql_connect_errno ()) {
printf ("Connection Failed:%s<br>", Mysql_connect_error ());
Exit ();
}
$query = "SET NAMES GB2312;";
$query. = "Select Curerent_user ();" Get the user name using the connection
$query. = "Select Name,phone from ContactInfo LIMIT 2";
/* Execute MULTI Query */
if ($mysqli->multi_query ($query)) {
do{
if ($result = $mysqli->store_result ()) {
$mysqli->store_result () Gets the first result set
while ($row = $result->fetch_row ()) {
foreach ($row as $data) {
echo $data. " ";
}
}
Echo ' <br> ';
}
if ($mysqli->more_results ()) {
echo "----------------------------<br>";
}
}while ($mysqli->next_result ());
}
/* Close Connection */
$mysqli->close ();
?>
Note:
In the example above, using the Multi_query () method in the Mysqli object executes three SQL commands at a time to get multiple result sets and traverse the data from them. If an error occurs during the processing of the command, the Multi_query () and Next_result () methods can cause problems.
The return value of the Multi_query () method, and the Mysqli property errno, error, info, and so on, are only related to the first SQL command, and it is not possible to determine whether the second and subsequent commands have encountered an error executing. Therefore, when executing the return value of the Multi_query () method is true, it does not mean that subsequent commands do not have errors at execution time.
The Multi_query () method in the Mysqli object