MySQL Retrieve Variable from Stored Procedure in PHP Pdo

MySQL retrieve variable from Stored Procedure in PHP PDO

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 Procedure
• PHP with output

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"

Retrieve multiple result sets from Stored Procedure with PHP

You could use variable variables to generate variable names on the fly:

$query = "CALL returnFunction()";
$p_sql = connection::conn()->prepare($query);
$p_sql->execute(); 
$i = 1;
do {
     $rows = $p_sql->fetchAll(PDO::FETCH_NUM);
     if ($rows) 
     {
          print_r($rows);
          ${"v$i"} = $rows;
     }
     $i++;
} while ($p_sql->nextRowset());

This will put each non-empty result set in a variable $v1, $v2 etc. But you should consider just using an array instead:

$query = "CALL returnFunction()";
$p_sql = connection::conn()->prepare($query);
$p_sql->execute(); 
$v = array();
do {
     $rows = $p_sql->fetchAll(PDO::FETCH_NUM);
     if ($rows) 
     {
          print_r($rows);
          $v[] = $rows;
     }
} while ($p_sql->nextRowset());

Note that if you want to store the empty result sets too then move the assignment outside of the if block.

PHP: How to get the output value of a MySql Stored Procedure?

Unfortunately, I could not find the right solution to catch the output of my stored procedure in PHP. According to this StackOverflow answer,

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

The source I discovered by @RyanVincent's comment also cites this bug report through 2005-2013 and this bug report from 2013-present.

I still needed a solution so I decided to turn my stored procedure into a stored function instead where I could easily catch the output using a simple SELECT statement before calling the function. This way, the return value will be selected which can be passed to PHP.

THE CODE:

// DEFINING THE FUNCTION:
$sql = '
    DROP FUNCTION IF EXISTS jani;
    CREATE FUNCTION jani ()
    RETURNS VARCHAR(3) DETERMINISTIC
    BEGIN
        RETURN "BBB";
    END';
$oDb->exec($sql);

// SELECTING THE RETURN VALUE OF THE FUNCTION:
$sql = 'SELECT jani();';
$do = $oDb->query($sql);

// DISPLAYING THE RESULT:
var_dump($do->fetchAll());

Thanks again for all the comments :) I hope this solution will help someone else spending hours on fixing their code.

PHP MySQL PDO stored procedures and INOUT parameters

User-defined variables are connection-specific.

See the documentation.

So no, there is no possibility of a race condition in a php execution context.

Handle output parameter from MySQL SP with PHP PDO

You can do either of these.

This is simply how to address an item in an array.

echo $r[0];

or

echo $r['@total']

Or it might be better to do

$r = $conn->query('select @total as total')->fetch();

echo $r['total']

as I am not sure if PHP would like an @ symbol in an array occurance.

---

Related Topics