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:
CREATE TABLE names ( ID INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name varchar(50) NOT NULL )
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)
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:
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):
Note: The output is the same with
Set all PHP Variables that will be used:
Define and Prepare the SQL procedure call:
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:
Get the SQL Variables into the PHP variables:
Note: maybe not the best way ;-/
Display the PHP variables