Next_result() and mysqli_fetch_assoc() collaboratively handle multiple result sets

When using MySQL, sometimes we will execute multiple SQL queries to obtain multiple result sets at once. In PHP, if you are using the mysqli extension, you can use the multi_query() method to execute multiple queries at once, and then process these result sets one by one through next_result() and mysqli_fetch_assoc() .

Below we will demonstrate this process through examples.

Scene Example

Suppose we have a database with two tables: users and orders . We want to query the contents in both tables at the same time.

 <?php
// Create a database connection
$mysqli = new mysqli("localhost", "username", "password", "database");

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

// Construct multiple linesSQLStatement
$sql = "SELECT id, name FROM users;";
$sql .= "SELECT id, user_id, total FROM orders;";

// usemulti_query()执行多条Statement
if ($mysqli->multi_query($sql)) {
    do {
        // usestore_result()Get the current result set
        if ($result = $mysqli->store_result()) {
            // Iterate through the current result set
            while ($row = $result->fetch_assoc()) {
                print_r($row);
            }
            $result->free(); // Release the current result set resource
        }

        // If there is a next result set，Continue
    } while ($mysqli->more_results() && $mysqli->next_result());
} else {
    echo "Query error: " . $mysqli->error;
}

// Close the database connection
$mysqli->close();
?>

Key Function Interpretation

multi_query()

This function allows you to execute multiple SQL statements in a single request. This is very useful in batch execution of queries and reducing the number of requests.

store_result()

This function is used to get the current result set and read it line by line with fetch_assoc() .

fetch_assoc()

Returns a row in the result set as an associative array, suitable for extracting data in a loop.

next_result()

This function is used to prepare the next result set. After processing the current result set, it must be called to enter the next result set.

Recommendations for use

• Every time a result set is processed, be sure to use free() to free the resource.

• Be careful to prevent SQL injection problems. If the query statement involves user input, be sure to use preprocessing statements.

• Check more_results() to make sure there are more result sets to process.

Practical scenarios

You can use multi_query() + next_result() in some scenarios:

• Backend report query multiple tables

• Execute stored procedures to return multiple result sets

• Optimize performance and reduce database interactions

summary

By combining multi_query() with next_result() and mysqli_fetch_assoc() , we can gracefully handle multiple result sets, improving program efficiency and code simplicity.

As shown above, whether you are developing enterprise applications or building your own services, mastering the use of these functions will help you operate the database more efficiently.

If you want to know more about content, please visit: https://gitbox.net for more practical tutorials and materials.