How to use next_result() to read the output parameters of stored procedures

Calling stored procedures is a common practice when using PHP to manipulate MySQL databases, especially when we want to encapsulate complex business logic at the database level. However, many developers often encounter situations where the output parameter value cannot be read when calling stored procedures with **output parameters (OUT parameters).

One of the key points is the use of next_result() . This article will explain in detail why this function needs to be called and how to correctly read the output parameters of stored procedures.

1. What is next_result() ?

When using the mysqli driver to call a stored procedure that returns multiple result sets (such as multiple SELECTs or both SELECT and output parameters), MySQL returns data in the form of "multiple result sets". The next_result() method is used to make the mysqli driver prepare to read the next result set.

If next_result() is not explicitly called and all result sets are traversed, some database results (such as output parameters or the final result set) will not be accessible.

2. Stored process examples

Let's first look at a simple MySQL stored procedure, which has an input parameter and an output parameter:

 DELIMITER $$

CREATE PROCEDURE get_user_email(IN user_id INT, OUT email VARCHAR(255))
BEGIN
    SELECT user_email INTO email FROM users WHERE id = user_id;
END $$

DELIMITER ;

This stored procedure querys its mailbox based on the user ID and returns it through the OUT parameter.

3. PHP calls the stored procedure

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

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

// 1. set up OUT Parameter variables
$mysqli->query("SET @email = ''");

// 2. Call stored procedures
$mysqli->query("CALL get_user_email(1, @email)");

// 3. Must use next_result() Clear the result set
while ($mysqli->more_results()) {
    $mysqli->next_result();
    $result = $mysqli->store_result();
    if ($result) {
        $result->free();
    }
}

// 4. Get OUT parameter
$result = $mysqli->query("SELECT @email AS user_email");
$row = $result->fetch_assoc();

echo "The user&#39;s email address is: " . $row['user_email'];

$mysqli->close();
?>

4. Why is next_result() the key?

After calling the CALL statement, MySQL may actually return one or more implicit result sets. If next_result() is not used to clear these result sets, subsequent queries (such as SELECT @email ) may be blocked or failed to execute.

Some developers will encounter the problem of "read output parameters are empty". In fact, it is not that the stored procedure has not been executed successfully, but that the previous result set has not been cleared, resulting in the subsequent query not being executed at all.

5. Advanced: Use preprocessing statements

If you are using preprocessing statements, you can also use similar logic:

 $stmt = $mysqli->prepare("CALL get_user_email(?, @email)");
$stmt->bind_param("i", $userId);
$userId = 1;
$stmt->execute();

// Clean up all result sets
do {
    if ($result = $stmt->get_result()) {
        $result->free();
    }
} while ($stmt->more_results() && $stmt->next_result());

// Get输出parameter
$result = $mysqli->query("SELECT @email AS user_email");
$row = $result->fetch_assoc();
echo "The user's email address is: " . $row['user_email'];

6. Summary

When you use CALL to execute stored procedures, you should call next_result() to iterate through all potential result sets regardless of whether the output parameters are used or multiple result sets are returned .

This is not only a good practice, but also a prerequisite to ensure that you can read the OUT parameters correctly. Keeping this in mind can help you avoid many headaches of "results not read" problems.

For more best practices for stored procedure calls, you can visit our development guide: https://gitbox.net/docs/mysql/stored-procedures