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

Retrieving MySQL Stored Procedure Variables in PHP Using PDO

Problem:

How can I retrieve the LAST_INSERT_ID() value from a MySQL stored procedure using PHP and PDO?

Procedure:

Consider the following PHP code:

<?php $stmt = $db->prepare("CALL simpleProcedure(:name,:returnid)");
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->bindParam(':returnid', $returnid, PDO::PARAM_INT, 11);
$stmt->execute();
echo $returnid;
?>

While this code may seem correct, it does not work due to a long-standing bug in PDO.

Solution:

To retrieve the output from a MySQL procedure, follow these steps:

Step 1: Run the Procedure

Run the procedure with your inputs and specify which MySQL variables will store the results. For example:

<?php $stmt = $db->prepare("CALL simpleProcedure(:name, @returnid)");
$stmt->bindValue(':name', $name, PDO::PARAM_STR);
$stmt->execute();
?>

Step 2: Query the MySQL Variables

Run a separate query to select the MySQL variables:

<?php $stmt = $db->query("SELECT @returnid");
$result = $stmt->fetchAll(PDO::FETCH_COLUMN, 0);
$returnid = $result[0];
?>

Example:

The following code includes a more detailed example showing the use of variables for different parameter types (IN, INOUT, OUT):

SQL Procedure:

CREATE PROCEDURE `demoSpInOutSqlVars`(
    IN     pInput_Param  INT, 
    /* --- */  
    INOUT  pInOut_Param  INT, 
    OUT    pOut_Param    INT)
BEGIN
    /*
     * Pass the full names of SQL User Variable for these parameters. e.g. '@varInOutParam'
     * These 'SQL user variables names' are the variables that Mysql will use for:
     *    1) finding values
     *    2) storing results
     *
     * It is similar to 'variable variables' in PHP.  
     */
     SET pInOut_Param      := ABS(pInput_Param) + ABS(pInOut_Param); 
     SET pOut_Param        := ABS(pInput_Param) * -3;                
END$$

PHP Code:

<?php // Bind PHP variables
$phpInParam = 5;                  
$phpInOutParam = 404;
$phpOutParam = null;

// Prepare the SQL procedure call
$sql = "call demoSpInOut(:phpInParam, 
                         @varInOutParam, 
                         @varOutParam)";

$stmt = $db->prepare($sql);
$stmt->bindParam(':phpInParam', $phpInParam, PDO::PARAM_INT);

// Set the SQL User INOUT variables
$db->exec("SET @varInOutParam = $phpInOutParam");

// Execute the procedure
$stmt->execute();

// Get the SQL variables into PHP variables
$sql = "SELECT @varInOutParam AS phpInOutParam,
               @varOutParam   AS phpOutParam
        FROM dual";

$results = $db->query($sql)->fetchAll(PDO::FETCH_ASSOC);
$phpInOutParam = $results[0]['phpInOutParam'];
$phpOutParam = $results[0]['phpOutParam'];

// Display the PHP variables
echo "phpInParam: $phpInParam<br>";
echo "phpInOutParam: $phpInOutParam<br>";
echo "phpOutParam: $phpOutParam<br>";
?>

By following these steps, you can effectively retrieve the output from MySQL stored procedures using PDO.

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