Current Location: Home> Latest Articles> How to use the next_result() function to encapsulate and process multiple query results and create a common multi-result processing function?

How to use the next_result() function to encapsulate and process multiple query results and create a common multi-result processing function?

gitbox 2025-05-02

In actual development, especially when using the MySQL multi-query function, you often encounter situations where multiple result sets need to be processed. If there is no unified processing method, manually calling next_result() and store_result() every time is not only troublesome, but also prone to errors.
To make the code more elegant and robust, we can encapsulate a general function and automatically process all result sets. Next, I will take you through how to do it step by step.

Understand the processing of next_result() and multi-result sets

When using mysqli for multiple queries (i.e. sending multiple SQL statements at once), each statement corresponds to a result set. Use next_result() to move mysqli object to the next result set, while store_result() is responsible for extracting the data from the current result set.

If these result sets are not processed correctly, it is easy to cause connection exceptions or the next query fails. Therefore, it is very important to clean up all result sets in a complete manner .

Encapsulate a general multi-result processing function

Here is an example showing how to encapsulate a function that processes all result sets and returns an array of all data:

 <?php

/**
 * Perform multiple queries and process all result sets
 *
 * @param mysqli $mysqli MySQLiConnect objects
 * @param string $multiQuery Multiple items to be executedSQLQuery statement(Separate with semicolons)
 * @return array Returns an array containing all result set data
 */
function executeMultiQuery(mysqli $mysqli, string $multiQuery): array
{
    $allResults = [];

    if ($mysqli->multi_query($multiQuery)) {
        do {
            if ($result = $mysqli->store_result()) {
                $data = [];
                while ($row = $result->fetch_assoc()) {
                    $data[] = $row;
                }
                $allResults[] = $data;
                $result->free();
            } else {
                // The current result may be without result set,for exampleUPDATEorINSERT
                if ($mysqli->errno) {
                    throw new Exception("MySQLmistake:" . $mysqli->error);
                }
                $allResults[] = null;
            }
        } while ($mysqli->more_results() && $mysqli->next_result());
    } else {
        throw new Exception("Multiple queries failed:" . $mysqli->error);
    }

    return $allResults;
}

// Example usage
$mysqli = new mysqli('localhost', 'root', 'password', 'testdb');

if ($mysqli->connect_errno) {
    die("Connection failed:" . $mysqli->connect_error);
}

try {
    $sql = "
        SELECT * FROM users;
        SELECT * FROM orders;
        UPDATE products SET stock = stock - 1 WHERE id = 5;
    ";
    $results = executeMultiQuery($mysqli, $sql);

    // Output all query results
    foreach ($results as $index => $resultSet) {
        echo "1. " . ($index + 1) . " A result set:<br>";
        if (is_array($resultSet)) {
            foreach ($resultSet as $row) {
                echo htmlspecialchars(json_encode($row)) . "<br>";
            }
        } else {
            echo "(No return result,For exampleUPDATEorINSERTStatement)<br>";
        }
        echo "<hr>";
    }
} catch (Exception $e) {
    echo "发生mistake:" . $e->getMessage();
}

$mysqli->close();
?>

Things to note

  1. Release the result set resources in time : It is very important to call $result->free() to avoid memory leakage.

  2. Error detection : If multi_query() or a certain round of store_result() error occurs, the exception should be caught in time to avoid further errors in subsequent operations.

  3. Adapting to operations without result sets : UPDATE , INSERT , DELETE and other statements do not return the result set, and special judgments are required during processing.

Conclusion

By encapsulating and processing multiple result sets, we not only make the code more concise, but also effectively avoid the problem of MySQL connection chaos. If your project needs to frequently handle multiple SQL queries, it is highly recommended to use a similar method for standardized encapsulation.

If you want to know more about the advanced use of multi-queries, such as transaction combined with multi-queries, or how to integrate such encapsulation in large projects, you can continue to follow our website: https://gitbox.net ! ??