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

Retrieving Stored Procedure Output Variables in PHP with PDO

Objective: Fetch the LAST_INSERT_ID() value from a MySQL stored procedure and assign it to a PHP variable.

Problem Statement

Despite the provided PHP code using PDO bindings, it fails to capture the LAST_INSERT_ID() output variable from the simpleProcedure stored procedure.

Explanation

Fetching output variables from MySQL stored procedures in PHP PDO involves a two-stage process:

1. Executing the stored procedure and assigning output variables to MySQL user variables.
2. Querying the MySQL user variables to retrieve their values into PHP variables.

Solution: Two-Stage Process

Stage 1: Executing the Procedure

$stmt = $db->prepare("CALL simpleProcedure(:name, @returnid)");
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->bindParam(':returnid', $returnid, PDO::PARAM_INT, 11, PDO::PARAM_INOUT);  // Note the PDO::PARAM_INOUT
$stmt->execute();

By binding the :returnid placeholder as INOUT, PDO will not only pass the PHP variable to the procedure but also update it with the output variable's value.

Stage 2: Retrieving the Output Variable

$sql = "SELECT @returnid AS output_id";
$result = $db->query($sql)->fetch(PDO::FETCH_ASSOC);

$lastInsertId = $result['output_id'];

Query the MySQL user variable @returnid to assign its value to the $lastInsertId PHP variable.

Note

Binding PHP variables to INOUT and OUT parameters for MySQL procedures can encounter runtime errors. It is recommended to only bind variables to IN parameters.

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