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.

<strong><span style="font-size: 20px;">mysqli_multi_query()</span></strong> Function

in The mysqli_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 the mysqli_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:

• $query represents the SQL statement to be queried

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

• $query represents 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:

<?php
    $host     = &#39;localhost&#39;;
    $username = &#39;root&#39;;
    $password = &#39;root&#39;;
    $dbname   = &#39;test&#39;;
    $mysql    = new Mysqli($host, $username, $password, $dbname);
    if($mysql -> connect_errno){
        die(&#39;数据库连接失败：&#39;.$mysql->connect_errno);
    }else{
        $sql    = &#39;select id,name from user;&#39;;  // SQL 语句
        $sql    .= &#39;select sex,age from user&#39;;  // 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 &#39;<hr>&#39;;
                }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 the mysqli_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 the mysqli_use_result() or mysqli_store_result() function.

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

If you want to process the next result set, you can use the mysqli_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 the mysqli_use_result() or mysqli_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:

<?php
    $host     = &#39;localhost&#39;;
    $username = &#39;root&#39;;
    $password = &#39;root&#39;;
    $dbname   = &#39;test&#39;;
    $link     = @mysqli_connect($host, $username, $password, $dbname);
    if($link){
        $sql    = &#39;select id,name from user;&#39;;  // SQL 语句
        $sql    .= &#39;select sex,age from user&#39;;  // SQL 语句
        $result = mysqli_multi_query($link, $sql);        // 执行 SQL 语句，并返回结果
        do{
            if($data = mysqli_use_result($link)){
                while ($row = mysqli_fetch_row($data)) {
                    print_r($row);
                }
                mysqli_free_result($data);
            }
            if(mysqli_more_results($link)){
                echo &#39;<hr>&#39;;
            }else{
                break;
            }
        } while (mysqli_next_result($link));
        mysqli_close($link);
    }else{
        echo &#39;数据库连接失败！&#39;;
    }
?>

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!