How to Retrieve OUT Parameters from MySQL Stored Procedures in PHP?

Accessing OUT Parameters in PHP MySQL Stored Procedures

Stored procedures are useful for encapsulating complex database operations. However, retrieving the output of an OUT parameter in PHP can be challenging. This article provides a detailed solution.

Solution

1. Connect to the Database: Establish a MySQL connection using the mysqli extension.

2. Call the Stored Procedure: Execute the stored procedure using the multi_query() method, specifying the OUT parameter(s) as shown in the syntax below:

   <code class="php">$mysqli->multi_query("CALL myproc($ivalue,@x);SELECT @x");</code>

3. Retrieve Results: The multi_query() method returns a boolean indicating success. If the call was successful:

   • Use store_result() to retrieve the results for the first query (the stored procedure execution).
   • Use fetch_row() to iterate over the result rows and access the OUT parameter value.

Example

Consider a stored procedure myproc with an IN parameter i and an OUT parameter j. Here's an example PHP code to access the OUT parameter:

<code class="php">$mysqli = new mysqli("HOST", "USR", "PWD", "DBNAME");
$ivalue = 1;
$res = $mysqli->multi_query("CALL myproc($ivalue,@x);SELECT @x");
if ($res) {
  $result = $mysqli->store_result();
  $row = $result->fetch_row();
  $output = $row[0]; // OUT parameter value
  $result->close();
}
$mysqli->close();</code>

This code demonstrates how to retrieve the OUT parameter j and store it in the $output variable.

The above is the detailed content of How to Retrieve OUT Parameters from MySQL Stored Procedures in PHP?. For more information, please follow other related articles on the PHP Chinese website!