MySQL retrieve variable from Stored Procedure in PHP PDO

Question

I've seen this question asked a load of times, but they're all really long, and I just can't get my head around what they're doing ... So, could someone tell me how to get the LAST_INSERT_ID() from this procedure into php using PDO:

Table:

CREATE TABLE names (
    ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY,
    name varchar(50) NOT NULL
)

Procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `simpleProcedure`(newname varchar(50), OUT returnid INT(11))
BEGIN
    INSERT INTO names (name) VALUES (newname);
    SET returnid = LAST_INSERT_ID();
END

PHP code I've tried:

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

But, probably obvious to someone who has more brain cells than me, this doesn't work. Any help appreciated.

Reference as to why I believe this SHOULD work:

http://www.php.net/pdo.prepared-statements (Example #4)

Answer 1

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"