Getting Output of LAST_INSERT_ID() in PHP and PDO

Example DB

CREATE TABLE `agents` (
  `agent_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`agent_id`)
)

Select allOpen in new window

Example stored procedure

CREATE
    PROCEDURE .`addagent`(IN agentname VARCHAR(255))
    BEGIN
	INSERT INTO `agents` (`name`) VALUES (agentname);
	SELECT @@IDENTITY;
    END

Select allOpen in new window

PHP Code

<?php

$dsn = 'mysql:dbname=ee;host=localhost';
$pdo = new PDO($dsn, 'user', 'password');

$result = $pdo->query("CALL agentname('James Bond')");
foreach($result as $r) {
  print_r($r);
}

Select allOpen in new window

Note the following in the Stored Procedure

SELECT @@IDENTITY;

Select allOpen in new window

Alternatively you can do

SELECT LAST_INSERT_ID();

Select allOpen in new window

I guess I wasn’t specific enough, this is for mysql and not ms sql, your syntax does not work for mysql. Mysql uses last_insert_id and I have the stored procedure working but have issue getting the value returned via pdo

That is MySQL
Note line 3 of the PHP listing

$dsn = 'mysql:dbname=ee;host=localhost';

Select allOpen in new window

That code was sucessfuly run against my MySQL database

but have issue getting the value returned via pdo

You retrieve it as you would a SELECT. To make this easier
Change line 5 of the stored proc to

SELECT @@IDENTITY AS lastId;

Select allOpen in new window

And in the PHP code change

$result = $pdo->query("CALL agentname('James Bond')");
foreach($result as $r) {
  print_r($r);
}

Select allOpen in new window

would change to

$result = $pdo->query("CALL addagent('Batman')");
$row = $result->fetch();
$id = is_array($row) ? $row['lastId'] : false;
if ($id) {
   echo "Successful insert [{$id}]";
}
else {
  echo "Failed";
}

Select allOpen in new window

Ok, i still don't see how i can call it in my scenario, to make it simple here is a short version of the stored procedure

REATE DEFINER=`root`@`%` PROCEDURE `sp_test`(
IN p_name varchar(45),
OUT LID Bigint
)
BEGIN

insert into tbl_test (name)
Values (p_name);
SET LID = LAST_INSERT_ID();
END

Select allOpen in new window

Sticking with my syntax  like

$sth = $this->db->prepare('CALL`sp_test`(?)');
$sth->bindParam(1, $data->name, PDO::PARAM_STR, 20);
$sth->execute();

Select allOpen in new window

How would i change the call to get my LID ?

my question was how to create a output parm in a prepare statment via bindParm and then get the value returned when using PDO

As per my example. Do a fetch into a row then access the lastId field.(modified for your code)

$row = $sth -> fetch();
echo $row ["lastId"];

Select allOpen in new window

In your stored procedure (as per my code)

SELECT @@IDENTITY AS lastId;

Select allOpen in new window

my question was how to create a output parm in a prepare statment via bindParm and then get the value returned when using PDO

I have answered this twice already - but will do so again.
You do a SELECT from your stored procedure
You do a normal fetch from your execute as you would on any other select statement.

Tried and that does not work...
using

$sth->execute();
 $row = $sth->fetch();
 echo $row["lastId"];

Select allOpen in new window

returns "Syntax error"

returns "Syntax error" where? Always provide as much information about what you observe so we can link it back to what we know about your project.
Here is my code revised to use a prepared statement

<?php

$dsn = 'mysql:dbname=db;host=localhost';
$pdo = new PDO($dsn, 'user', 'password');

$statement = $pdo->prepare("CALL addagent('Jason Bourne')");
$result = $statement->execute();
$row = $statement->fetch();

$id = is_array($row) ? $row['lastId'] : false;
if ($id) {
   echo "Successful insert [{$id}]";
}
else {
  echo "Failed";
}

Select allOpen in new window

This returns the following

Successful insert [22]

Select allOpen in new window

Therefore, the code works - if it is not working for you then it is an implementation issue and you will need to post the code you are using along with a detailed list of errors - including an indication which lines of the code you post the error relates to.

just guessing : seems likely you kept the "SET" line rather than using a select so the stored procedure retrieves the LID but does not return anything. in that case the workbench displays the set value which is confusing.

nope, my procedure has the select

CREATE DEFINER=`root`@`%` PROCEDURE `sp_test`(
IN p_name varchar(45)
)
BEGIN

insert into tbl_test (name)
Values (p_name);
SELECT LAST_INSERT_ID() as LastID;
END

Select allOpen in new window

and i call it from my sql editor like this in workbench

CALL`sp_test`('Mueller');

Select allOpen in new window

which returns me the LastID, problem is how to get this value in PDO after calling a stored procedure via prepare statments

Change

SELECT LAST_INSERT_ID() as LastID;

Select allOpen in new window

To

SELECT @@IDENTITY as LastID;

Select allOpen in new window

problem is how to get this value in PDO after calling a stored procedure via prepare statments

I have given you working code to show you how to do this - see the code in my second to last post.

That does not make a difference as its not the returned number which causes issue but how to get it in PDO.  But i humor you and changed it and still get syntax error
Here is my stored Procedure

CREATE DEFINER=`root`@`%` PROCEDURE `sp_test`(
IN p_name varchar(45)
)
BEGIN

insert into tbl_test (name)
Values (p_name);
SELECT @@IDENTITY as LastID;
END

Select allOpen in new window

and thats my code

  public function insertTest()
    {
        $statement = $this->db->prepare("CALL `sp_test`('Mueller')");
        $result = $statement->execute();
        $row = $statement->fetch();

        $id = is_array($row) ? $row['lastId'] : false;
        if ($id) {
            echo "Successful insert [{$id}]";
        } else {
            echo "Failed";
        }
    }

Select allOpen in new window

Technically speaking you should be able to retrieve the Output parameter of your Stored Procedure automatically by using bindParam. This works fine against MS SQL Servers, but due to a bug (either in MySQL or the PDO Driver) it simply doesn't work for MySQL. Assuming your SP has an input and output parameter, you should be able to do this:

$name = "Chris";

$stmt = $dbh->prepare("CALL sp_test(?, ?)");
$stmt->bindParam(1, $name);
$stmt->bindParam(2, $id, PDO::PARAM_INT, 11);
$stmt->execute();

var_dump($id);

Select allOpen in new window

It doesn't work, so you need a work around. Julian has shown you one way to do that, but if you still want to use your original SP with the output parameter, then you need a little additional code. Effectively, once you've called the stored procedure, you then need to send another query to the DB to retrieve the Output value. Something like this:

$sth = $this->db->prepare('CALL sp_test(?, @id)');
$sth->bindParam(1, $data->name);
$sth->execute();

// now we need the additional code to retrieve the OUTPUT value:
$id = $this->db->query("SELECT @id")->fetchColumn();

Select allOpen in new window

SELECT @@IDENTITY as LastID;

Select allOpen in new window

IS NOT THE SAME AS

SELECT @@IDENTITY as lastId;

Select allOpen in new window

Case matters.

You are returning LastID
And looking for lastId

This is why it is so important to post your implementation (code) so we can see how you have interpreted the advice given.

Change either the SP to reflect the lastId in the PHP script or the PHP to be LastID as per the SP.

This might be one small issue but not the real problem so when i use

  
    public function insertCustomer($data)
    {
        $statement = $this->db->prepare("CALL `sp_test`('Mueller')");
        $result = $statement->execute();
        $row = $statement->fetch();


        $id = is_array($row) ? $row['LastId'] : false;
        if ($id) {
            echo "Successful insert [{$id}]";
        } else {
            echo "Failed";
        }
    }

Select allOpen in new window

I still get Syntax Error

When you use:

$statement = $this->db->prepare("insert into `tbl_test` (name) Values('Mueller2')");

It's an INSERT statement, so there is nothing to fetch back, so calling fetch() will error.

That was a error on my part i tried different things and forgot to go from insert back to SP, i updated my comment to reflect it which still returns syntax error.
When i use your code i get "Cannot pass parameter 2 by reference". Also do i need to create an output param in stored procedure ? For now my SP and table looks like this

CREATE DEFINER=`root`@`%` PROCEDURE `sp_test`(
IN p_name varchar(45)
)
BEGIN

insert into tbl_test (name)
Values (p_name);
SELECT @@IDENTITY as LastID;
END

CREATE TABLE `tbl_test` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(45) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `id_UNIQUE` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=29 DEFAULT CHARSET=latin1;

Select allOpen in new window

Chris, thanks this one worked, if you edit your last response to only have your code it will be less confusing since i selected it as answer because it had the full working code

For the record the code posted here does work. Here is the full working source
Stored Procedure

CREATE
    PROCEDURE `ee`.`addagent`(IN agentname VARCHAR(255))
    BEGIN
	INSERT INTO `agents` (`name`) VALUES (agentname);
	SELECT LAST_INSERT_ID() AS lastId;
    END

Select allOpen in new window

PHP

<?php

$dsn = 'mysql:dbname=db;host=localhost';
$pdo = new PDO($dsn, 'user', 'password');

$statement = $pdo->prepare("CALL addagent('Jack Bauer')");
$result = $statement->execute();
$row = $statement->fetch();

$id = is_array($row) ? $row['lastId'] : false;
if ($id) {
   echo "Successful insert [{$id}]";
}
else {
  echo "Failed";
}

Select allOpen in new window

The Author states he received a syntax error with code from this post although he does not give us any indication what line the error occurred on nor what the content of the error was we can possibly guess that it has something to do with the fact that his function declaration included the word 'public' - which is only valid if this is in a class.

The above solution is a valid working solution - the problems experienced would therefore be related to implementation.

First of it did not work for me, also the solution I picked worked and was a simple fix to my code.

Yes, but the selected solution requires TWO queries to get the desired result - which is not necessary - it only requires one query.
The reason it does not work for you is that somewhere in your implementation there is an implementation error resulting in the syntax error. We should be focusing on what that problem is. To find that we need to know exactly what the syntax error was and the code to which it pertains.