Getting Output of LAST_INSERT_ID() in PHP and PDO

Here is my problem, i have a stored Procedure that creates a new customer and i call it from my RestAPI which works fine. So that
the User can make changes etc i need the CustomerId of the new record i just created. the Customerid is an Autoincrement field
so it is supported via the "LAST_INSERT_ID()" my stored procedure works fine when i call it from my workbench, my issue is how to
call it form PHP when using PDO.
$sth = $this->db->prepare('CALL `oss`.`sp_customer_insert`(?,?,?,?)');

            $sth->bindParam(1, $data->quicken_id, PDO::PARAM_STR, 20);
            $sth->bindParam(2, $data->old_id, PDO::PARAM_STR, 25);
            $sth->bindParam(3, $data->status, PDO::PARAM_STR, 10);
            $sth->bindParam(4, $data->name, PDO::PARAM_STR, 60);
$sth->execute();

Open in new window

So question is where and how do i specify the output field in my prepare statment and how do i retrieve the value of LID
since mysql does not return it by default
Confused CoderAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Julian HansenCommented:
Example DB
CREATE TABLE `agents` (
  `agent_id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(30) DEFAULT NULL,
  PRIMARY KEY (`agent_id`)
)

Open in new window

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

Open 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);
}

Open in new window


Note the following in the Stored Procedure
SELECT @@IDENTITY;

Open in new window

Alternatively you can do
SELECT LAST_INSERT_ID();

Open in new window

0
Confused CoderAuthor Commented:
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
0
Julian HansenCommented:
That is MySQL
Note line 3 of the PHP listing
$dsn = 'mysql:dbname=ee;host=localhost';

Open in new window

That code was sucessfuly run against my MySQL database
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Julian HansenCommented:
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;

Open in new window

And in the PHP code change
$result = $pdo->query("CALL agentname('James Bond')");
foreach($result as $r) {
  print_r($r);
}

Open 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";
}

Open in new window

0
Confused CoderAuthor Commented:
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

Open 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();

Open in new window

How would i change the call to get my LID ?
0
Confused CoderAuthor Commented:
my question was how to create a output parm in a prepare statment via bindParm and then get the value returned when using PDO
0
Julian HansenCommented:
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"];

Open in new window

In your stored procedure (as per my code)
SELECT @@IDENTITY AS lastId;

Open in new window

0
Julian HansenCommented:
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.
0
Confused CoderAuthor Commented:
Tried and that does not work...
using
$sth->execute();
 $row = $sth->fetch();
 echo $row["lastId"];

Open in new window

returns "Syntax error"
0
Julian HansenCommented:
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";
}

Open in new window

This returns the following
Successful insert [22]

Open 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.
0
skullnobrainsCommented:
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.
0
Confused CoderAuthor Commented:
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

Open in new window

and i call it from my sql editor like this in workbench
CALL`sp_test`('Mueller');

Open 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
0
Julian HansenCommented:
Change
SELECT LAST_INSERT_ID() as LastID;

Open in new window

To
SELECT @@IDENTITY as LastID;

Open in new window

0
Julian HansenCommented:
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.
0
Confused CoderAuthor Commented:
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

Open 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";
        }
    }

Open in new window

0
Chris StanyonWebDevCommented:
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);

Open 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();

Open in new window

0
Julian HansenCommented:
SELECT @@IDENTITY as LastID;

Open in new window

IS NOT THE SAME AS
SELECT @@IDENTITY as lastId;

Open 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.
0
Confused CoderAuthor Commented:
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";
        }
    }

Open in new window

I still get Syntax Error
0
Chris StanyonWebDevCommented:
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.
0
Confused CoderAuthor Commented:
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;

Open in new window

0
Chris StanyonWebDevCommented:
Not sure who's code you're referring to. If you're referring to mine, then you use an output parameter:

CREATE PROCEDURE `sp_test`(IN p_name VARCHAR(45), OUT id INT(11))
	BEGIN
		INSERT INTO `tbl_test` (`name`) VALUES (p_name);
		SET id = LAST_INSERT_ID();
	END

Open in new window

$stmt = $this->db->prepare("CALL sp_test(:name, @id)");
$stmt->bindParam('name', $data->name);
$stmt->execute();
$id = $this->db->query("SELECT @id")->fetchColumn();

Open in new window

If you're referring to Julians, then you don't need an output parameter. The return value is created with the SELECT part:

CREATE PROCEDURE `sp_test`(IN p_name VARCHAR(45))
	BEGIN
		INSERT INTO `tbl_test` (`name`) VALUES (p_name);
		SELECT @@IDENTITY as LastID;
	END

Open in new window

$stmt = $this->db->prepare("CALL sp_test(:name)");
$stmt->bindParam('name', $data->name);
$stmt->execute();
$id = $stmt->fetchColumn();

Open in new window

Nothing in your previous code shows you passing any parameters, so I can't see why you'd be getting the Cannot pass parameter 2 by reference error. This usually happens if you try and pass a value into a bindParam() call instead of a variable:

$sth->bindParam(1, "Chris"); // this will error
$sth->bindParam(1, $name); // this won't
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Confused CoderAuthor Commented:
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
0
Julian HansenCommented:
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

Open 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";
}

Open 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.
0
Confused CoderAuthor Commented:
First of it did not work for me, also the solution I picked worked and was a simple fix to my code.
0
Julian HansenCommented:
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.
1
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.