next_result() combined with mysqli_multi_query() complete multi-query execution process

gitbox 2025-05-02

When using MySQL databases in PHP, we often need to execute multiple queries. mysqli_multi_query() provides the ability to execute multiple queries, but after multiple queries are executed, how to get the results of each query one by one? At this time, the next_result() method comes in handy. This article will explain in detail how to implement a complete multi-query execution process through next_result() and mysqli_multi_query() .

What are mysqli_multi_query() and next_result() ?

mysqli_multi_query() : This is a method provided by MySQLi to execute multiple SQL queries. This method executes multiple SQL queries at once and returns a Boolean value to indicate whether the execution is successful.
Example:
```
 $mysqli->multi_query($query);
```
next_result() : This is another method provided by MySQLi to get the next result set in multi-query results. If the query has multiple result sets (such as multiple SELECT queries), you need to use next_result() to get each result set in sequence.
Example:
```
 $mysqli->next_result();
```

Execute multiple queries using mysqli_multi_query()

First, we need to connect to the database and make sure that the MySQL connection has been established:

 <?php
$mysqli = new mysqli('localhost', 'user', 'password', 'database');

// Check if the connection is successful
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}
?>

Next, we build a SQL statement containing multiple queries:

 <?php
$query = "SELECT * FROM users;
          SELECT * FROM products;
          SELECT * FROM orders;";
?>

In the above code, we have three queries: one query users table, one query products table, and one query orders table. We want to execute these queries and get the results of each query in turn.

Use mysqli_multi_query() to execute a query

Use mysqli_multi_query() to execute multiple queries and use next_result() to get the result set of each query:

 <?php
// Perform multiple queries
if ($mysqli->multi_query($query)) {
    // Looping each result set
    do {
        if ($result = $mysqli->store_result()) {
            // Get and display the result set for each query
            while ($row = $result->fetch_assoc()) {
                echo "ID: " . $row['id'] . " - Name: " . $row['name'] . "<br>";
            }
            $result->free();
        }

        // If there are more result sets，Continue to process
        if ($mysqli->more_results()) {
            $mysqli->next_result();
        }
    } while ($mysqli->more_results());
} else {
    echo "Error: " . $mysqli->error;
}
?>

Code explanation

Execute query : $mysqli->multi_query($query) is used to execute multiple queries. If the query succeeds, it returns true and then enters the do-while loop.
Process each result set : $mysqli->store_result() is used to get the result set of the current query. Iterate through each row of data through fetch_assoc() .
Check for more result sets : $mysqli->more_results() is used to check if there are more result sets. If so, use next_result() to continue to get the next result set.
Free resources : After each process of a result set, we use free() to free up memory.

Things to note

Query order : mysqli_multi_query() executes multiple queries in order, and the execution order is consistent with the order in SQL statements.
Error handling : When executing multiple queries, if any query occurs, the entire process may fail. Make sure to handle errors well.
Data security : Use prepared statements to prevent SQL injection problems. When executing multiple queries, it is best to use preprocessing statements to protect data security.

URL replacement example

If you are involved in a URL in the query and need to replace the domain name with gitbox.net , for example:

 <?php
$query = "SELECT * FROM websites WHERE url = 'http://example.com';";
?>

In this case, you simply replace the domain name of the URL with gitbox.net , for example:

 <?php
$query = "SELECT * FROM websites WHERE url = 'http://gitbox.net';";
?>

This way, you can flexibly replace all domain names with gitbox.net when processing queries.

next_result