How to Retrieve Variables from MySQL Stored Procedures in PHP PDO?

Retrieving Variables from MySQL Stored Procedures in PHP PDO

This discussion aims to provide a comprehensive solution to retrieving variables from MySQL stored procedures using PHP PDO.

Issue

While attempting to obtain the LAST_INSERT_ID() from a MySQL stored procedure in PHP PDO, it was encountered that the implemented code was not functioning.

Solution

Unfortunately, this issue can be attributed to a long-standing bug in PHP's handling of MySQL stored procedures with OUT parameters.

Multi-Step Process

The workaround involves a two-step process:

1. Execute Procedure with Input Variables: Execute the procedure with your inputs and specify the MySQL variables to store the result.
2. Query Variables: Run a separate query to select the MySQL variables that contain the results.

Procedure Call with Variables

In the provided PHP code, replace the problematic binding with the following:

$stmt = $db->prepare("CALL simpleProcedure(:name, @returnid)");

This binds the PHP variable $name to the input parameter and creates a MySQL variable named @returnid to store the output. The @ symbol denotes that returnid is a MySQL variable.

Selecting Variables

After the procedure execution, use the following query to retrieve the @returnid variable:

$result = $db->query("SELECT @returnid AS result_id");
$row = $result->fetch();
$returnid = $row['result_id'];

Alternative Approach with OUT Parameters

If a more complete solution is desired, including the use of INOUT and OUT parameters, the following code sample and tips can be utilized:

Tips:

• Set PDO's ATTR_EMULATE_PREPARES to true for better error handling.
• Bind PHP variables only to IN parameters.

Sample Code:

SQL Procedure:

CREATE PROCEDURE demoSpInOutSqlVars(IN pInput_Param INT, INOUT pInOut_Param INT, OUT pOut_Param INT)
BEGIN
    SET pInOut_Param = ABS(pInput_Param) + ABS(pInOut_Param);
    SET pOut_Param = ABS(pInput_Param) * -3;
END

PHP Code:

$phpInParam = 5;
$phpInOutParam = 404;
$phpOutParam = null;

$stmt = $db->prepare("CALL demoSpInOutSqlVars(:phpInParam, @varInOutParam, @varOutParam)");
$stmt->bindParam(':phpInParam', $phpInParam, PDO::PARAM_INT);
$db->exec("SET @varInOutParam = $phpInOutParam");
$stmt->execute();

$result = $db->query("SELECT @varInOutParam AS phpInOutParam, @varOutParam AS phpOutParam FROM dual");
$row = $result->fetch();

$phpInOutParam = $row['phpInOutParam'];
$phpOutParam = $row['phpOutParam'];

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