How Do You Retrieve Output Parameter Values from PHP Stored Procedures?

Retrieving Output Parameter Values in PHP Stored Procedures

In the realm of PHP and MySQL stored procedures, there may come a time when you need to access the value of an output parameter, known as an "out" parameter. While documentation may seem sparse, there are ways to achieve this with the PHP MySQLi extension.

Assume you have a stored procedure defined as myproc(IN i int, OUT j int), where the i parameter is an input and j is an output parameter. To access the output value in PHP, you can use the following steps:

<code class="php">// Establish a connection to the database
$mysqli = new mysqli("HOST", "USR", "PWD", "DBNAME");

// Input parameter value
$ivalue = 1;

// Execute the stored procedure and capture the result
$res = $mysqli->multi_query("CALL myproc($ivalue, @x);SELECT @x");

// Check if the execution was successful
if ($res) {
    $results = 0;

    // Iterate through the results
    do {
        // Store the result
        if ($result = $mysqli->store_result()) {
            printf("<b>Result #%u</b>:<br/>", ++$results);

            // Fetch and display the rows
            while ($row = $result->fetch_row()) {
                foreach ($row as $cell) echo $cell, " ";
            }
            $result->close();
        }
    } while ($mysqli->next_result());
}

// Close the connection
$mysqli->close();</code>

This script leverages MySQLi's multi_query() and store_result() functions to execute the stored procedure and retrieve both the input and output values. The output value is accessed by including @x in the SELECT query, where x is the name of the output parameter in the stored procedure.

The above is the detailed content of How Do You Retrieve Output Parameter Values from PHP Stored Procedures?. For more information, please follow other related articles on the PHP Chinese website!