A typical combination of next_result() and mysqli_multi_query()

In PHP, the mysqli_multi_query() function can execute multiple SQL query statements, and the next_result() function can help us process these query results one by one. Generally, multiple queries can be sent in the same request, and we can get data from the result set of each query through functions such as mysqli_fetch_assoc() , mysqli_fetch_row() , etc. This article will introduce how to use the next_result() function and mysqli_multi_query() to process multiple query results.

1. Preparation: Database connection

Before using the mysqli_multi_query() function, we first need to establish a MySQL database connection. Here is a code example for connecting to the database:

 <?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "database_name";

// Create a connection
$conn = new mysqli($servername, $username, $password, $dbname);

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

2. Execute multiple query statements

The mysqli_multi_query() function allows us to execute multiple SQL query statements at once, separated by semicolons ( ; ). Here is an example of using mysqli_multi_query() to perform multiple queries:

 <?php
$sql = "SELECT * FROM users; SELECT * FROM orders;";

if ($conn->multi_query($sql)) {
    do {
        // Get query results
        if ($result = $conn->store_result()) {
            // Iterate over the result set
            while ($row = $result->fetch_assoc()) {
                echo "userID: " . $row['user_id'] . " - user名: " . $row['username'] . "<br>";
            }
            $result->free();
        }
        // If there is the next result set，Call next_result() Move to the next result set
    } while ($conn->next_result());
} else {
    echo "Query failed: " . $conn->error;
}
?>

In the above code, mysqli_multi_query() executes two queries. The first query gets all users from the users table, and the second query gets all order data from the orders table. The do-while loop ensures that each query result set is processed.

3. Use next_result() to process multiple result sets

The mysqli_multi_query() function returns multiple result sets when multiple queries are executed. The function of the next_result() function is to move to the next result set. Every time next_result() is called, it will check whether there is a next result set. If so, the function will return true , otherwise false .

In the above code, we use a do-while loop and next_result() to make sure that all query result sets can be traversed until there are no more results.

4. Error handling

If an error occurs while executing multiple queries, we can get the error message through $conn->error . In the above code, we simply output error information when the query fails, and of course you can also perform more complex error handling based on the actual situation.

5. Close the connection

After executing all queries and processing all result sets, remember to close the database connection:

 <?php
$conn->close();
?>

6. Conclusion

With the mysqli_multi_query() and next_result() functions, PHP can easily handle the result sets of multiple SQL queries. This approach is very useful for performing batch queries and applications that require multiple data results. Hope this article can help you understand how to use these two functions to implement the processing of multi-query results.

Additional Instructions

If you need more help, you can visit our official documentation: https://gitbox.net/docs/mysql for more information.