How to execute multiple SQL commands at once when learning PHP database?

In the previous article, I brought you "Common functions for obtaining SQL query results in PHP (detailed examples)", which introduced in detail several commonly used functions when querying databases in PHP. function. In this article, let’s take a look at how to execute multiple SQL commands at once. I hope everyone has to help!

In the previous article, we introduced you to the common functions for obtaining SQL query results. Querying data through different functions returns different forms. Run mysqli_query() The function can only execute one SQL command at a time. There is a function in PHP that allows the code to execute multiple SQL commands at once, that is the mysqli_multi_query() function. Next, let us learn about the mysqli_multi_query() function.

mysqli_multi_query()Function

in Themysqli_query()function we introduced earlier can only execute one SQL command at a time when running this function. However, when we want to execute multiple SQL commands, the mysqli_query() function cannot satisfy us. At this time we need to use themysqli_multi_query()function to execute multiple SQL commands at one time.

mysqli_multi_query()The syntax format of the function is as follows:

mysqli::multi_query(string $query)

This is object-oriented writing, and the following is process-oriented writing:

mysqli_multi_query(mysqli $link, string $query)

where It should be noted that:

• $queryrepresents the SQL statement to be queried

• $linkrepresents the link identifier returned by using the mysqli_connect() function

• $queryrepresents the parameters, which can contain multiple SQL commands, each SQL Use semicolons;to separate commands. This function returns TRUE if the first SQL command is executed without errors, otherwise it returns FALSE.

Next, let’s look at the use of the mysqli_multi_query() function through an example to execute multiple SQL commands. The example is as follows:

 connect_errno){ die('数据库连接失败：'.$mysql->connect_errno); }else{ $sql = 'select id,name from user;'; // SQL 语句 $sql .= 'select sex,age from user'; // SQL 语句 if($mysql -> multi_query($sql)){ do{ if ($result = $mysql -> store_result()) { while ($row = $result->fetch_row()) { print_r($row); } $result->free(); } if ($mysql -> more_results()) { echo '
'; }else{ break; } } while ($mysql -> next_result()); } $mysql -> close(); } ?>

Output results:

The above results are completed by executing multiple SQL commands through the mysqli_multi_query() function.

What we need to pay attention to is:

Because themysqli_multi_query()function can connect to execute one or more queries, and each SQL command may return a result, each result set needs to be obtained when necessary. Therefore, there have been some changes in the processing of the results returned by this function. The result of the first query command must be read using themysqli_use_result()ormysqli_store_result()function.

You can also use themysqli_store_result()function to get all the results back to the client immediately, and it is more efficient. Additionally, you can use themysqli_more_results()function to check if there are other result sets.

If you want to process the next result set, you can use themysqli_next_result()function to obtain the next result set. The function returns TRUE when there is a next result set, and returns FALSE when there is not. When there is a next result set, you also need to use themysqli_use_result()ormysqli_store_result()function to read the contents of the result set.

The above uses the object-oriented method. Next, let’s look at the process-oriented method. The example is as follows:

'; }else{ break; } } while (mysqli_next_result($link)); mysqli_close($link); }else{ echo '数据库连接失败！'; } ?>

The output result is the same as the above result, so we use the mysqli_multi_query() function Complete multiple SQL commands at once.

If you are interested, you can click on "PHP Video Tutorial" to learn more about PHP knowledge.

The above is the detailed content of How to execute multiple SQL commands at once when learning PHP database?. For more information, please follow other related articles on the PHP Chinese website!