How do I retrieve MySQL stored procedure variables using PDO in PHP?

Retrieving MySQL Stored Procedure Variables using PDO in PHP

Retrieving the value of LAST_INSERT_ID() from a MySQL stored procedure can be a daunting task. Despite ample documentation online, the process often proves elusive to many developers. This article aims to demystify this issue, providing a step-by-step guide to retrieving the variable's value using PHP PDO.

PHP Procedure Parameters with 'IN', 'INOUT', and 'OUT' Parameters

The key to retrieving the variable is understanding the two-stage process involved. First, the procedure must be executed with the necessary input parameters and the variables that will store the results. Subsequently, a separate query is executed to retrieve the values from the designated variables.

Two-Stage Process for Retrieving MySQL Stored Procedures Using PDO

To demonstrate this process, let's consider the following scenario:

• SQL Procedure with 'IN', 'INOUT', and 'OUT' parameters
• PHP code to execute the procedure and retrieve the variables' values

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); /* always positive sum  */
     SET pOut_Param        := ABS(pInput_Param) * -3;                /* always negative * 3  */ 
END

PHP Code:

// DB Connection
$db = appDIC('getDbConnection', 'default'); // get the default db connection
$db->setAttribute(PDO::ATTR_EMULATE_PREPARES, true);
$db->setAttribute(PDO::ATTR_DEFAULT_FETCH_MODE, PDO::FETCH_ASSOC);

// Set PHP Variables
$phpInParam = 5;
$phpInOutParam = 404; // PHP InOut variable
$phpOutParam = null; // PHP Out variable

// Define and Prepare the SQL procedure call
$sql = "call demoSpInOut(:phpInParam,
                         @varInOutParam, /* mysql variable name will be read and updated */
                         @varOutParam)"; /* mysql variable name that will be written to  */

$stmt = $db->prepare($sql);

// Bind PHP Variables and Set SQL Variables
$stmt->bindParam(':phpInParam', $phpInParam, PDO::PARAM_INT);
$db->exec("SET @varInOutParam = $phpInOutParam"); // This is safe as it just sets the value into the MySql variable.

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

// Get the SQL Variables into the PHP variables
$sql = "SELECT @varInOutParam AS phpInOutParam,
               @varOutParam AS phpOutParam
        FROM dual";
$results = current($db->query($sql)->fetchAll());

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

// Display the PHP variables
echo "phpInParam = " . $phpInParam . "\n";
echo "phpInOutParam = " . $phpInOutParam . "\n";
echo "phpOutParam = " . $phpOutParam . "\n";

Additional Notes:

• Binding PHP variables (in this case, $phpInParam) to SQL IN parameters is straightforward and done via PDO's bindParam() method.
• Binding PHP variables to SQL INOUT and OUT parameters is not supported and will result in runtime errors.
• The trick to retrieving SQL procedure values is to use SQL user variables to hold the results.
• Running PDO in 'emulate prepares mode' is recommended for debugging and error detection.

Conclusion:

Retrieving MySQL stored procedure variables in PHP PDO is a multifaceted process that involves executing the procedure and retrieving values from SQL user variables. By following the steps outlined above, developers can efficiently access the required variables from MySQL stored procedures using PDO.

The above is the detailed content of How do I retrieve MySQL stored procedure variables using PDO in PHP?. For more information, please follow other related articles on the PHP Chinese website!