Combining next_result() in CodeIgniter to handle multi-statement query

gitbox 2025-05-06

During the development process using CodeIgniter (particularly CodeIgniter 3), sometimes we need to execute statement blocks (Multi-Query) containing multiple SQL queries. For example, we might want to call multiple stored procedures at once, or execute multiple queries in one request to improve efficiency.

However, when using multi-statement query, a common problem is: only the first result set can be accessed, and other result sets cannot be obtained . This is because CodeIgniter does not automatically process multiple result sets when using MySQLi drivers. To solve this problem, we need to manually call the $this->db->next_result() method to clean up the previous result set and prepare the system for the next one.

This article will show you how to correctly use next_result() in CodeIgniter to handle multi-statement queries.

Basic example: Use multi_query and next_result()

Suppose you have a stored procedure file, or execute SQL like the following directly in the controller:

 $sql = "
    CALL get_users(); 
    CALL get_orders();
";

To execute this type of multi-statement SQL, you can use the query() method and manually manage the result set:

 $result = $this->db->query("CALL get_users();");

if ($result) {
    $users = $result->result();

    // Clean up the first result set
    $result->free_result();
    $this->db->next_result();

    // Get the second result set
    $result2 = $this->db->query("CALL get_orders();");

    if ($result2) {
        $orders = $result2->result();

        // Clean up again
        $result2->free_result();
        $this->db->next_result();
    }
}

This method is suitable for calling only one stored procedure at a time and then manually executing the next statement. But if you want to execute multiple SQL statements at once, for example:

 $sql = "
    CALL get_users(); 
    CALL get_orders(); 
    CALL get_products();
";

It can be implemented with the help of MySQLi's native multi_query() function.

Using MySQLi's multi_query method

In CodeIgniter, if you are using the MySQLi driver, you can do this:

 $mysqli = $this->db->conn_id; // Get the underlying MySQLi Connect to resources

$sql = "
    CALL get_users(); 
    CALL get_orders(); 
    CALL get_products();
";

if ($mysqli->multi_query($sql)) {
    do {
        if ($result = $mysqli->store_result()) {
            // Process the current result set
            $data[] = $result->fetch_all(MYSQLI_ASSOC);
            $result->free();
        }
    } while ($mysqli->more_results() && $mysqli->next_result());
}

This code will fetch multiple result sets in turn and use next_result() to prepare the next one.

Recommendations for correct usage in CodeIgniter

Although you can use multi_query() directly, it is highly recommended to execute multiple statements separately , especially if you have high requirements for security and stability. For database operations, the following practices are recommended:

Call free_result() and next_result() after each query to clean up the connection.
Avoid writing multiple CALLs directly in the model, but instead encapsulate them into a single call, and logical control is placed in the PHP layer.
If you have to use multi_query() , make sure you process all returned result sets and avoid the "Commands out of sync" error.

Example: Encapsulation into model method

You can encapsulate this process into a model method and return an array containing multiple datasets:

 public function get_multi_data()
{
    $mysqli = $this->db->conn_id;

    $sql = "
        CALL get_users(); 
        CALL get_orders(); 
        CALL get_products();
    ";

    $data = [];

    if ($mysqli->multi_query($sql)) {
        do {
            if ($result = $mysqli->store_result()) {
                $data[] = $result->fetch_all(MYSQLI_ASSOC);
                $result->free();
            }
        } while ($mysqli->more_results() && $mysqli->next_result());
    }

    return $data;
}

In the controller you can call it like this:

 $this->load->model('Data_model');
$result_sets = $this->Data_model->get_multi_data();

$users = $result_sets[0];
$orders = $result_sets[1];
$products = $result_sets[2];

Tips: Pay attention to log output when debugging

When debugging multi-statement query, it is recommended to enable the logging function of CodeIgniter and observe the content in application/logs to facilitate troubleshooting errors.

next_result