PDO::lastInsertId

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

PDO::lastInsertId — Returns the ID of the last inserted row or sequence value

Description

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

Returns the ID of the last inserted row, or the last value from a sequence object, depending on the underlying driver. For example, PDO_PGSQL() requires you to specify the name of a sequence object for the name parameter.

Note: This method may not return a meaningful or consistent result across different PDO drivers, because the underlying database may not even support the notion of auto-increment fields or sequences.

Parameters

name: Name of the sequence object from which the ID should be returned.

Return Values

If a sequence name was not specified for the name parameter, PDO::lastInsertId() returns a string representing the row ID of the last row that was inserted into the database.

If a sequence name was specified for the name parameter, PDO::lastInsertId() returns a string representing the last value retrieved from the specified sequence object.

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

PDO::prepare

PDO::getAvailableDrivers

Last updated: Fri, 27 Jun 2008

add a note User Contributed Notes
PDO::lastInsertId

ed at hicklinslade dot com
26-May-2008 05: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
15-May-2008 06: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
13-May-2008 08:47


This function is not available for MSSQL either.

Yonatan Ben-Nes
17-May-2007 05: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 10: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 01: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 02: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, 27 Jun 2008