PDO::lastInsertId

(PHP 5 >= 5.1.0, PHP 7, PECL pdo >= 0.1.0)

PDO::lastInsertId Возвращает ID последней вставленной строки или последовательное значение

Описание

public string PDO::lastInsertId ([ string $name = NULL ] )

Возвращает ID последней вставленной строки либо последнее значение, которое выдал объект последовательности. Что именно будет возвращено, зависит от нижележащего драйвера. Например, метод PDO_PGSQL требует задать имя объекта последовательности для параметра name.

Замечание:

В зависимости от PDO драйвера этот метод может вообще не выдать осмысленного результата, так как база данных может не поддерживать автоинкремент или последовательности.

Список параметров

name

Имя объекта последовательности, который должен выдать ID.

Возвращаемые значения

Если объект последовательности для name не задан, PDO::lastInsertId() вернет строку представляющую ID последней добавленной в базу записи.

Если же объект последовательности для name задан, PDO::lastInsertId() вернет строку представляющую последнее значение, полученное от этого объекта.

Если PDO драйвер не поддерживает эту возможность, PDO::lastInsertId() запишет IM001 в SQLSTATE.

Коментарии

Simple example:
<?php
try {
   
$dbh = new PDO('mysql:host=localhost;dbname=test''root''passowd');

   
$smf $dbh->prepare("INSERT INTO test (`numer`) VALUES (?)");
 
   
$a mt_rand(1100);
   
$smf->bindParam(1$aPDO::PARAM_INT);
   
$smf->execute();
   print 
$dbh->lastInsertId().'<br />';

   
$a mt_rand(1100);
   
$smf->bindParam(1$aPDO::PARAM_INT);
   
$smf->execute();
   print 
$dbh->lastInsertId();

   
$dbh null;
} catch (
PDOException $e) {
   print 
"Error!: " $e->getMessage() . "<br/>";
   die();
}
?>
2005-12-20 08:10:19
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
in case anyone was wondering
something like

$val = 5;
$sql = "REPLACE table (column) VALUES (:val)";
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':val', $val, PDO::PARAM_INT);
$stmt->execute();
$lastId = $dbh->lastInsertId();

will return the last inserted id, whether the record was replaced or simply inserted

the REPLACE syntax, simply inserts, or deletes > inserts
so lastInsertId() still works

refer to http://mysql.com/doc/refman/5.0/en/replace.html
for REPLACE usage
2006-08-18 08:34:51
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
Автор:
It should be noted that, at least for MySQL using InnoDB tables, with transactions PDO will report the last insert id as 0 after the commit, the real ids are only reported before committing.

(As a side note, MySQL keeps the ID number incremented after a rollback).
2007-01-22 04:40:46
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
Автор:
It should be mentioned that this function DOES NOT retrieve the ID (Primary key) of the row but it's OID instead.

So if you use one of the latest PostgreSQL versions this function won't help you unless you add OID to the table specifically when you create it.
2007-05-17 12:05:20
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
Автор:
This function is not available for MSSQL either.
2008-05-13 15:47:11
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
Автор:
As said by Dennis Du Kroger, in this situation the function will return 0. 

But you can retrieve the last inserted Id executing a query asking for the function LAST_INSERT_ID() (at least in MySQL)

Try this:

($o_db is the declared adapter)

$last_id = $o_db->fetchAll('SELECT LAST_INSERT_ID() as last_id');
       
$last_id = intval($last_id[0]['last_id']);
2008-05-15 13:28:50
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
In response to Yonatan Ben-Nes, it does appear that using the latest versions of PHP 5.x and PostgreSQL 8.x, the driver will return a "meaningful" ID (rather than an OID), provided you pass the name of the corresponding sequence.

So, if you created a table as follows:

CREATE TABLE "user" (
"id" SERIAL PRIMARY KEY NOT NULL,
"username" character varying(32)
);

PostgreSQL will (by default) create a sequence called 'user_id_seq'.

You can then do something like:

$strTable = "user":
$last_insert_id = $objPDO->lastInsertId("$strTable_id_seq);

This does appear to function as expected. What is a little unclear to me is whether this simply returns the current value of the sequence; if it does, this isn't a particularly reliable indicator as to the id of the record your code just inserted, especially if your site or application is especially high traffic.
2008-05-26 12:05:19
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
Автор:
Beware of lastInsertId() when working with transactions in mysql. The following code returns 0 instead of the insert id.

<?php
try {
   
$dbh = new PDO('mysql:host=localhost;dbname=test''username''password');

   
$stmt $dbh->prepare("INSERT INTO test (name, email) VALUES(?,?)");

    try {
       
$dbh->beginTransaction();
       
$tmt->execute( array('user''user@example.com'));
       
$dbh->commit();
        print 
$dbh->lastInsertId();
    } catch(
PDOExecption $e) {
       
$dbh->rollback();
        print 
"Error!: " $e->getMessage() . "</br>";
    }
} catch( 
PDOExecption $e ) {
    print 
"Error!: " $e->getMessage() . "</br>";
}
?>

When no exception is thrown, lastInsertId returns 0. However, if lastInsertId is called before calling commit, the right id is returned.
2008-08-15 17:49:11
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
For PostgreSQL you can still use the old solution to return the last Id of an INSERT, selecting the currval of a table's id_sequence.

The code below shows a working function (which is easy adaptale into another class, etc).

<?php
// -------------------------
// Last Insert ID for PDO with PostgreSQL
// -------------------------
function pgsqlLastInsertId($sqlQuery$pdoObject)
{
   
// Checks if query is an insert and gets table name
   
if( preg_match("/^INSERT[\t\n ]+INTO[\t\n ]+([a-z0-9\_\-]+)/is"$sqlQuery$tablename) )
    {
       
// Gets this table's last sequence value
       
$query "SELECT currval('" $tablename[1] . "_id_seq') AS last_value";

       
$temp_q_id $pdoObject->prepare($query);
       
$temp_q_id->execute();

        if(
$temp_q_id)
        {
           
$temp_result $temp_q_id->fetch(PDO::FETCH_ASSOC);
            return ( 
$temp_result ) ? $temp_result['last_value'] : false;
        }
    }

    return 
false;
}

?>

Example of use:

<?php
// ... connects to a PostgreSQL DB
$pdoObject = new PDO('pgsql:host=localhost;dbname=mydb''user''pass');

$sql 'INSERT INTO table (column) VALUES (\'some_value\');';
$result $pdoObject->prepare($sql);
$result->execute();

echo 
'Last Insert ID: ' pgsqlLastInsertId($sql$pdoObject);

?>
2008-10-06 20:21:58
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
Workaround for the fact that MSSQL does not provide lastInsertId().  This is locale-independent by design.

<?php
$mixRc 
false;
try {
   
// Issue a compound command, 2nd part outputs the inserted Id
   
$strQuery =
   
'INSERT INTO t1 (f1,f2) VALUES(v1,v2); SELECT @@IDENTITY AS mixLastId';
   
// Yup, your eyes are ok, NOT exec but query!!!
   
$objSth $objDb->query($strQuery);
   
$mixRc = (is_object($objSth) and $objSth->errorCode() == '00000');
}
catch (
PDOException $objException) {
   
$pdoMsg $objException->getMessage();
   
$pdoMsg iconv("ISO-8859-1""UTF-8"$pdoMsg);
   
$strMessage 'insertRecord: Failed ' .
                 
$strQuery ', Error Message: ' $pdoMsg;
   
doLog(__FILE____LINE__$strMessage);
    throw new 
core_exception_database($strMessage);
}
if (
$mixRc === false) return false;

// The compound command delivers a multi-rowset statement handle
// Move past the first (invalid) rowset from the INSERT command
$objSth->nextRowset();
// Pick up the first row of the rowset from "SELECT @@IDENTITY"
$rowTd $objSth->fetch(PDO::FETCH_NUM);
if (!
is_array($rowTd)) {
   
doLog(__FILE____LINE__,
       
'insertRecord: $objSth->fetch() returns %s'gettype($rowTd));
    return 
false;
}
$objSth->closeCursor();
$strLastRowId trim($rowTd[0]); // trim() for trailing Nullbyte
// Integers are returned stringified, format depends on locale
// Generally ends with ",00" or ".00" - trim that off
$strLastRowId preg_replace('/[,.]0+$/'''$strLastRowId);
// Remove any remaining "." or "," for thousands
$strLastRowId preg_replace('/[,.]/'''$strLastRowId);
// A GUID, which contains no "," or ".", will be left unchanged
return $strLastRowId;
?>
2008-12-11 14:03:57
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
I think I get a nice solution in Postgres to get the ID using the RETURNING that comes with Postgress since version 8.2. In the example below, I add to my insert clause the "returning" along with the primary key of my table, then after the execute, I do a fetch getting an array with the value of the last inserted id. 

<?php
   
public function insert($employee){

       
$sqlQuery "INSERT INTO employee(user_id,name,address,city) VALUES(:user_id,:name,:address,:city) RETURNING employee_id";

       
$statement $this->prepare($sqlQuery);

       
$a ="2002-03-11 12:01AM" ;

       
$statement->bindParam(":user_id"$employee->getUserId(), PDO::PARAM_INT);
       
$statement->bindParam(":name"$employee->getName(), PDO::PARAM_STR);
       
$statement->bindParam(":address"$employee->getAddress(), PDO::PARAM_STR);
       
$statement->bindParam(":city"$employee->getCity(), PDO::PARAM_STR);
       
$statement->execute();
       
       
$result $statement->fetch(PDO::FETCH_ASSOC);
        return 
$result["employee_id"];

    }
?>
2011-02-24 09:48:54
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
If you're accessing MSSQL/SQL Server 2008 R2 (or higher) from Linux via FreeTDS there's a slightly neater way of getting the last insert ID than the solution(s) outlined below.

The specific SQL involved is outlined here:

http://msdn.microsoft.com/en-us/library/ms177564.aspx

So for example, with a table containing the two columns (product_id, product_name) where product_id is a uniqueidentifier or something similar you could do the following.

<?php

// Assume $dbh connection handle is already established

$sql "INSERT INTO product (product_name) OUTPUT INSERTED.product_id VALUES (?)";

$sth $dbh->prepare($sql);

$sth->execute(array('widgets'));

$temp $sth->fetch(PDO::FETCH_ASSOC);

?>

Then $temp will contain an array like:

Array
(
    [product_id] => E1DA1CB0-676A-4CD9-A22C-90C9D4E81914
)

Just be warned that there are some issues relating to how uniqueidentifier columns are handled by PDO_DBLIB/FreeTDS depending on the TDS version you choose that have only been fixed as of PHP 5.3.7.

Information regarding this and the patch can be found here:

https://bugs.php.net/bug.php?id=54167
2011-08-30 02:04:01
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
Remember, if you use a transaction you should use lastInsertId BEFORE you commit
otherwise it will return 0
2012-02-22 15:24:05
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
This function is now compatible with the newer MS SQL driver. http://msdn.microsoft.com/en-us/library/ff628155(v=sql.105)
2012-09-13 22:23:54
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
Easiest solution I've found for MSSQL to obtain the last inserted ID is

<?php
$STH 
$DBH->query("SELECT CAST(COALESCE(SCOPE_IDENTITY(), @@IDENTITY) AS int)");
$STH->execute();
$result $STH->fetch();
print 
$result[0];
?>
2013-01-02 04:34:29
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
@nour

You need to call lastInsertId before you commit
2013-10-07 11:33:21
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
In the Return Values part:

If the PDO driver does not support this capability, PDO::lastInsertId() triggers an IM001 SQLSTATE.
--------------------------------------------------------------------------------

So what's the solution for this....?
And I got this error:
-------------------------------------------------------------------------------
PHP Warning:  PDO::lastInsertId(): SQLSTATE[IM001]: Driver does not support this function: driver does not support lastInsertId() in xxxx
-------------------------------------------------------------------------------
I'm using IBM db2 odbc driver...
2015-09-23 11:49:45
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
Автор:
beware when mixing auto-incremented and explicit IDs!
Given a fresh table "tbl", executing 

insert into tbl values (0, 'kaeptn blaubaer'); --auto increment (-> 1)
insert into tbl values (16, 'pipi langstrumpf'); --explicit id (-> 16)
select LAST_INSERT_ID();

will return 1, which is not the value of the last insert. its the value from the last auto-increment insert!

(using mysql)
2016-06-20 15:18:34
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
Автор:
WARNING for PostgreSQL users! In response to the comment by ed at hicklinslade dot com, who wrote:

...
$last_insert_id = $objPDO->lastInsertId("$strTable_id_seq);

This does appear to function as expected. What is a little unclear to me is whether this simply returns the current value of the sequence; if it does, this isn't a particularly reliable indicator as to the id of the record your code just inserted, especially if your site or application is especially high traffic.
...

NEVER ever use lastInsertId() with PostgreSQL sequences, ESPECIALLY when your application's insert/update load is high. PostgreSQL sequences are non-transactional (a natural design feature to avoid exclusive locking which otherwise produces unacceptable performance). This means that any concurrent transaction incrementing the same sequence will render the value returned by lastInsertId() invalid with respect to the last insert by your transaction. Example:

Transaction 1 inserts with nextval('some_seq') yielding 100;
Concurrent transaction 2 inserts with nextval('some_seq') yielding 101;
Transaction 1 calls lastInsertId(), expecting 100, BUT GETS 101.

This PDO method is braindead for PostgreSQL, always use INSERT ... RETURNING instead. Regards.
2016-12-20 13:23:19
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
About the connections created through classes

eg: db::SQL()->query();
then db::SQL()->lastInsertId();

it will create a new connection and will not return the last ID inserted. it is better to include a PDO connection file (or directly the logins) and work with it to get the last ID properly.

$db = new PDO(logins);
$db->query();
$db->lastInsertId();
2017-02-08 19:26:12
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
To save time for some of you.

When using MySQL or MariaDB while inserting multiple rows in a single query (INSERT INTO table (a,b,c) VALUES (1,2,3), (2,3,4), ...) to a table with auto_increment column, PDO::lastInsertId does NOT return the autogenerated id of the last row. Instead, the FIRST generated id is returned. This may very well be explained by taking a look at MySQL and MariaDB's documentation.

Quotations from their respective documentations, 

MySQL:
"With no argument, LAST_INSERT_ID() returns a BIGINT UNSIGNED (64-bit) value representing the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement."

MariaDB:
"LAST_INSERT_ID() (no arguments) returns the first automatically generated value successfully inserted for an AUTO_INCREMENT column as a result of the most recently executed INSERT statement."

This is clearly not what lastInsertId's own documentation states. Hopefully this will save someone from debugging the cause of id mismatch.

tl;dr (MySQL | Mariadb) + multi row insert + PDO::lastInsertId = first autogenerated id

Behaviour tested using MariaDB 10.2.6 32-bit, PHP 5.6.31 32-bit and mysqlnd 5.0.11 running on windows 7.
2017-12-11 14:26:24
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
On version 7.0.9 I've implemented lastInsertId without having to name the sequence for PostgreSQL (I've also have done it for 5.6.can'trememberthenumber, but I can't find the PR).

Can someone update the documentation about it?

Here is the Pull Request: https://github.com/php/php-src/pull/2014
2018-07-23 22:41:00
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
$dbh->commit(); 
print $dbh->lastInsertId(); 
The above will always return zero (0)
So it is important to call $dbh->lastInsertId();  before commiting transaction 

the above should be modified as 
print $dbh->lastInsertId(); 
$dbh->commit();
2018-12-25 09:06:53
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html
MySQL/MariaDB users, be aware that although this function returns a string, leading zeroes are NOT preserved if your column has ZEROFILL property.
2021-12-10 20:27:52
http://php5.kiev.ua/manual/ru/pdo.lastinsertid.html

    Поддержать сайт на родительском проекте КГБ