PDO::lastInsertId

(PHP 5 >= 5.1.0, PECL pdo:0.1-1.0.3)

PDO::lastInsertId — 最後に挿入された行の ID あるいはシーケンスの値を返す

説明

string PDO::lastInsertId ([ string $name ] )

最後に挿入された行の ID、あるいはシーケンスオブジェクトから次の値をを返します。これは、構成しているドライバに依存します。例えば PDO_PGSQL() の場合、name パラメータにシーケンスオブジェクト名を指定する必要があります。

注意: このメソッドは、異なる PDO ドライバ間で意味のあるもしくは一貫性のある結果を返さないかも知れません。構成しているデータベースが自動インクリメントフィールド、もしくはシーケンスの概念をサポートしていないかも知れないためです。

パラメータ

name: ID が返されるべきシーケンスオブジェクト名を指定します。

返り値

もし name パラメータにシーケンス名が指定されなかった場合、 PDO::lastInsertId() はデータベースに挿入された最後の行の行IDに相当する文字列を返します。

もし name パラメータにシーケンス名が指定された場合、 PDO::lastInsertId() は指定されたシーケンスオブジェクトから取得した最後の値に相当する文字列を返します。

もし PDO ドライバがサポートしていない場合、 PDO::lastInsertId() は IM001 SQLSTATE を発生させます。

PDO::prepare

PDO::getAvailableDrivers

Last updated: Fri, 22 Aug 2008

add a note User Contributed Notes
PDO::lastInsertId

Nour
16-Aug-2008 06:49


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.

lod
18-Jul-2008 12:41


Using Postgresql calling this function is identical to doing (with appropriate quoting):



PDO::query("SELECT CURRVAL($name)");



That's the exact query performed by PDO source code used.

(version checked current at time of writing, pgsql_driver.c,v 1.68 2007/12/31)



In particular this means that in normal operation you are still in auto-commit mode and CURRVAL is subject to race conditions.



To use this function safely in Postgresql you MUST wrap it in a beginTransaction() commit() block.

ed at hicklinslade dot com
27-May-2008 01:05


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.

Xavier Arnaus
16-May-2008 02:28


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']);

Steven L
14-May-2008 04:47


This function is not available for MSSQL either.

Yonatan Ben-Nes
18-May-2007 01:05


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.

Dennis Du Krøger
22-Jan-2007 07:40


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).

dave at dtracorp dot com
18-Aug-2006 09:34


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

opik at opik dot ru
20-Dec-2005 11:10


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(1, 100);

   $smf->bindParam(1, $a, PDO::PARAM_INT);

   $smf->execute();

   print $dbh->lastInsertId().'<br />';



   $a = mt_rand(1, 100);

   $smf->bindParam(1, $a, PDO::PARAM_INT);

   $smf->execute();

   print $dbh->lastInsertId();



   $dbh = null;

} catch (PDOException $e) {

   print "Error!: " . $e->getMessage() . "<br/>";

   die();

}

?>

add a note

PDO::prepare

PDO::getAvailableDrivers

Last updated: Fri, 22 Aug 2008