PDO::lastInsertId

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

PDO::lastInsertId — Liefert die ID der zuletzt eingefügten Zeile oder den Wert der zuletzt eingefügten Sequenz

Beschreibung

public PDO::lastInsertId(?string $name = null): string|false

Gibt je nach zugrundeliegendem Treiber die ID der zuletzt eingefügten Zeile oder den letzten Wert eines Sequenzobjekts zurück. Beispielsweise erlaubt PDO_PGSQL, für den Parameter name den Namen eines beliebigen Sequenzobjekts anzugeben.

Hinweis:
Es ist möglich, dass diese Methode bei verschiedenen PDO-Treibern nicht zu einem sinnvollen oder konsistenten Ergebnis führt, da die zugrundeliegende Datenbank unter Umständen nicht einmal das Konzept der automatischen Inkrementierung von Feldern oder Sequenzen unterstützt.

Parameter-Liste

name: Der Name des Sequenzobjekts, dessen ID zurückgegeben werden soll.

Rückgabewerte

Wenn für den Parameter name kein Sequenzname angegeben wurde, gibt PDO::lastInsertId() eine Zeichenkette zurück, die die Zeilen-ID der zuletzt in die Datenbank eingefügten Zeile darstellt.

Wenn für den Parameter name ein Sequenzname angegeben wurde, gibt PDO::lastInsertId() eine Zeichenkette zurück, die den zuletzt aus dem angegebenen Sequenzobjekt abgerufenen Wert darstellt.

Wenn der PDO-Treiber diese Funktionalität nicht unterstützt, löst PDO::lastInsertId() den SQLSTATE IM001 aus.

Fehler/Exceptions

Gibt einen Fehler der Stufe E_WARNING aus, wenn das Attribut PDO::ATTR_ERRMODE auf PDO::ERRMODE_WARNING gesetzt ist.

Löst eine PDOException aus, wenn das Attribut PDO::ATTR_ERRMODE auf PDO::ERRMODE_EXCEPTION gesetzt ist.

Found A Problem?

Learn How To Improve This Page • Submit a Pull Request • Report a Bug

＋add a note

User Contributed Notes 12 notes

down

252

spark at limao dot com dot br ¶

12 years ago

Remember, if you use a transaction you should use lastInsertId BEFORE you commit
otherwise it will return 0

down

toinenkayt (ta at ta) [iwonderr] gmail d ¶

7 years ago

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.

down

Nour ¶

16 years ago

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.

down

dave at dtracorp dot com ¶

18 years ago

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

down

warezthebeef at gmail dot com ¶

13 years ago

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

down

Yonatan Ben-Nes ¶

17 years ago

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.

down

ruben02 at hotmail dot com ¶

13 years ago

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"];



    }

?>

down

enclaved ¶

8 years ago

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.

down

nafsinvk at gmail dot com ¶

5 years ago

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

down

noel dot mcavoy at gmail dot com ¶

12 years ago

This function is now compatible with the newer MS SQL driver. http://msdn.microsoft.com/en-us/library/ff628155(v=sql.105)

down

phpmanual at NOSPAM dot headbank dot co dot uk ¶

3 years ago

MySQL/MariaDB users, be aware that although this function returns a string, leading zeroes are NOT preserved if your column has ZEROFILL property.

down

timer timer five at gmail dot com ¶

7 years ago

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

＋add a note