MySQL retrieve variable from Stored Procedure in PHP PDO

by

It turns out that this is a bug that has been going on for a long time… since 2005!

Here is the original bug report: 2005 through to 2013. And here is the new bug report: From 2013 to the present.

There are various approaches to getting the answer returned, I found one of them and demonstrate it…

The ‘trick’ is that to get the output from a ‘mysql’ procedure. It is a ‘two stage’ process.

  • The first part is to run the procedure with your inputs, and also tell it what MYSQL variables to store the result in.

  • Then, you run a separate query to ‘select’ those ‘mysql’ variables.

It is described quite clearly here: php-calling-mysql-stored-procedures

Update (Jan 2017):

Here is an example showing the use of variables for ‘IN’, ‘INOUT’ and ‘OUT’ Mysql procedure parameters.

Before we start here are some tips:

  • When developing: Run PDO in ’emulates mode’ as it is more reliable at determining errors in the procedure call.
  • Only bind PHP variables to the procedure ‘IN’ parameters.

You will get some really odd runtime errors when you try binding variables to INOUT and OUT parameters.

As usual I tend to provide rather more comments than are required ;-/

Runtime Environment (XAMPP):

  • PHP: 5.4.4
  • Mysql: 5.5.16

Source Code:

SQL Code:

CREATE PROCEDURE `demoSpInOutSqlVars`(IN     pInput_Param  INT, /* PHP Variable will bind to this*/   
                                      /* --- */  
                                      INOUT  pInOut_Param  INT, /* contains name of the SQL User variable that will be read and set by mysql */
                                      OUT    pOut_Param    INT) /* contains name of the SQL User variable that will be set by mysql */
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);

Note: The output is the same with EMULATE_PREPARES = false.

Set all PHP Variables that will be used:

$phpInParam     = 5;                  
$phpInOutParam  = 404;          /* PHP InOut variable  ==> read and should be changed  */
$phpOutParam    = null;         /* PHP Out   variable  ==> should be changed           */

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:

  • 1) bind the PHP variables

    $stmt->bindParam(‘:phpInParam’, $phpInParam, PDO::PARAM_INT);

  • 2) Set the SQL User INOUT variables

    $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'];

Note: maybe not the best way ;-/

Display the PHP variables

"$phpInParam:"     => "5"
"$phpInOutParam:"  => "409"
"$phpOutParam:"    => "-15"

More Related Contents:

  1. mysqli or PDO – what are the pros and cons? [closed]
  2. How to get numeric types from MySQL using PDO?
  3. How can I use PDO to fetch a results array in PHP?
  4. PHP PDOException: “SQLSTATE[HY093]: Invalid parameter number”
  5. Causes of MySQL error 2014 Cannot execute queries while other unbuffered queries are active
  6. Use bound parameter multiple times
  7. What is the difference between MySQL, MySQLi and PDO? [closed]
  8. MySQL check if a table exists without throwing an exception
  9. how safe are PDO prepared statements
  10. Can I use a PDO prepared statement to bind an identifier (a table or field name) or a syntax keyword?
  11. PHP PDO – Num Rows
  12. Replacing mysql_* functions with PDO and prepared statements
  13. PDO + MySQL and broken UTF-8 encoding
  14. PDOstatement (MySQL): inserting value 0 into a bit(1) field results in 1 written in table
  15. php code to test pdo is available?
  16. Understanding pdo mysql transactions
  17. Calling stored procedure with Out parameter using PDO
  18. When *not* to use prepared statements?
  19. How to insert an array into a single MySQL Prepared statement w/ PHP and PDO
  20. Setting a connect timeout with PDO
  21. What is the best way to insert multiple rows in PHP PDO MYSQL?
  22. PDO MySQL: Insert multiple rows in one query
  23. How to bind LIKE values using the PDO extension?
  24. Error while using PDO prepared statements and LIMIT in query [duplicate]
  25. Warning: PDO::__construct(): [2002] No such file or directory (trying to connect via unix:///tmp/mysql.sock) in
  26. SQLSTATE[HY093]: Invalid parameter number: parameter was not defined
  27. PDO::rowCount VS COUNT(*)
  28. PHP 7 RC3: How to install missing MySQL PDO
  29. PHP: mysql v mysqli v pdo [closed]
  30. PDO and MySQL ‘between’

Leave a Comment