How to correctly call next_result() function in PHP

gitbox 2025-04-28

In PHP, the next_result() function is a function provided by the MySQLi extension library to handle multiple query result sets. Usually when multiple queries are executed, this function is used to obtain subsequent result sets to avoid unexpected behavior when multiple queries are executed.

1. What is next_result() ?

The next_result() function is part of MySQLi and is specifically designed to correctly traverse all result sets when executing multiple SQL queries. Its purpose is to get the next result set until no more result sets are available.

2. How to use the next_result() function?

To better understand the use of next_result() , first we need an example containing multiple queries. Suppose we are processing a MySQL request containing multiple SQL queries, we can use the multi_query() method to execute multiple queries at once and use next_result() to get all the result sets one by one.

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

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

// Multiple query statements
$sql = "SELECT * FROM users; SELECT * FROM orders; SELECT * FROM products;";

// Perform multiple queries
if ($mysqli->multi_query($sql)) {
    // Get the first result set
    if ($result = $mysqli->store_result()) {
        echo "Users Table Results:<br>";
        while ($row = $result->fetch_assoc()) {
            echo "User: " . $row["username"] . "<br>";
        }
        $result->free();
    }

    // Get the next result set
    while ($mysqli->next_result()) {
        if ($result = $mysqli->store_result()) {
            // Process the results of each query
            echo "<br>Next Result:<br>";
            while ($row = $result->fetch_assoc()) {
                echo "ID: " . $row["id"] . " - Name: " . $row["name"] . "<br>";
            }
            $result->free();
        }
    }
}

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

In the above code, first we execute three SQL queries through multi_query() . Then, use next_result() to iterate through each result set in sequence. In this way, we can process multiple query results in the same database connection without re-executing each query.

3. Important precautions

Call order : Every time next_result() is called, MySQLi will automatically move to the next result set. If there are no more result sets, the function returns false .
Free resources : After obtaining and processing each result set, remember to use the free() method to release the query results to avoid memory leakage.
Error handling : Although the multi_query() method may encounter errors when executing multiple queries, next_result() will only be processed after the previous query is completely processed. Therefore, make sure to process errors for each query and perform exception handling in due course.
Performance considerations : Using multiple queries will process multiple queries on the database side, but at the application layer, we must read and process the results of each query one by one. Therefore, ensure that the structure and data volume of the query are moderate to avoid affecting performance.

4. Sample application: Processing data from different tables

In actual development, multiple queries are used and the results of each query are processed in sequence. Common application scenarios include reading data from different tables and displaying them on the page. Here is a simple example showing how to combine data from multiple tables.

 <?php
// Assume this is a user management system，First query the user table，Check the order table again，And obtain product information
$sql = "SELECT id, username FROM users; SELECT id, order_name FROM orders; SELECT id, product_name FROM products;";

// Execute a query
if ($mysqli->multi_query($sql)) {
    // Get user data
    if ($result = $mysqli->store_result()) {
        echo "Users:<br>";
        while ($row = $result->fetch_assoc()) {
            echo "ID: " . $row["id"] . " - Username: " . $row["username"] . "<br>";
        }
        $result->free();
    }

    // Get order data
    while ($mysqli->next_result()) {
        if ($result = $mysqli->store_result()) {
            echo "<br>Orders:<br>";
            while ($row = $result->fetch_assoc()) {
                echo "Order ID: " . $row["id"] . " - Order Name: " . $row["order_name"] . "<br>";
            }
            $result->free();
        }
    }

    // Obtain product data
    while ($mysqli->next_result()) {
        if ($result = $mysqli->store_result()) {
            echo "<br>Products:<br>";
            while ($row = $result->fetch_assoc()) {
                echo "Product ID: " . $row["id"] . " - Product Name: " . $row["product_name"] . "<br>";
            }
            $result->free();
        }
    }
}

$mysqli->close();
?>

In this example, we successfully fetched data from the three tables of users , orders and products , and processed the results of each query one by one.

next_result