3 tips to avoid resource unrelease when using next_result()

gitbox 2025-05-06

In PHP, next_result() is a common method in MySQLi extensions and is usually used when executing multiple queries. However, many developers may ignore the release of resources when using next_result() , resulting in memory leaks and performance issues. To avoid this, this article will share 3 practical tips to help you ensure that resources are released correctly when using next_result() .

1. Make sure to clean up all result sets

When multiple queries are executed, next_result() reads and processes the result set of each query. If the result set is not released in time, they will always take up memory. To avoid this, make sure to call the free_result() method after each processing of the result.

Sample code :

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

// Perform multiple queries
$mysqli->multi_query("SELECT * FROM table1; SELECT * FROM table2;");

// Process the first result set
do {
    if ($result = $mysqli->store_result()) {
        // Processing data
        while ($row = $result->fetch_assoc()) {
            echo $row['column_name'];
        }
        // Clean up the first result set
        $result->free();
    }
    // Go to the next result set
} while ($mysqli->next_result());
?>

In the above code, after we process the first query result set, we call $result->free() to free up the resource. Then, the result set of the next query is continued by next_result() .

2. Handle errors with caution when using multi_query()

When multiple queries are executed using multi_query() , if one of the queries fails, the subsequent query results may not be released normally. In this case, you can ensure that the result set of each query is cleaned up by checking the returned error message.

Sample code :

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

// Execute multiple query statements
$query = "SELECT * FROM table1; SELECT * FROM table2;";
if ($mysqli->multi_query($query)) {
    do {
        if ($result = $mysqli->store_result()) {
            // Processing data
            while ($row = $result->fetch_assoc()) {
                echo $row['column_name'];
            }
            // Clean up the result set
            $result->free();
        }
    } while ($mysqli->next_result());
} else {
    echo "Error: " . $mysqli->error;
}
?>

In this example, if multi_query() fails to execute, the code outputs an error message instead of continuing to execute subsequent queries, which prevents unfreed result sets from occupying resources.

3. Use transactions and manually control query order

In some scenarios, if the order of queries is related to transaction processing, it is recommended to use transactions to ensure smooth execution of queries and the release of each query can be manually managed. This can avoid resource unrelease issues due to incorrect query order.

Sample code :

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

// Start a transaction
$mysqli->begin_transaction();

try {
    // Execute the first query
    $mysqli->query("SELECT * FROM table1;");
    
    // Execute the second query
    $mysqli->query("SELECT * FROM table2;");
    
    // Submit transactions
    $mysqli->commit();
} catch (Exception $e) {
    // If an error occurs, the transaction will be rolled back
    $mysqli->rollback();
    echo "Failed: " . $e->getMessage();
} finally {
    // Clean up all result sets
    while ($mysqli->more_results()) {
        $mysqli->next_result();
    }
}
?>

In this example, we use transactions to process multiple queries, and after execution is complete, make sure that next_result() is called to release the result set of all queries.

Summarize

To avoid the problem of unrelease of resources when using next_result() , make sure you follow the following points:

After each process of query results, call free_result() to free the resource.
When executing multiple queries using multi_query() , be careful about error handling and ensure that the results of each query are properly cleaned up.
When using transactions, manually control the query order and make sure that all result sets are cleaned after all queries are executed.

By following these tips, you can effectively avoid unreleased resources, thereby improving application performance and stability.

next_result