PHP Doku:: Returns the ID of the last inserted row or sequence value - pdo.lastinsertid.html

Verlauf / Chronik / History: (1) anzeigen

Sie sind hier:
Doku-StartseitePHP-HandbuchFunktionsreferenzDatenbankerweiterungenAbstraktionsebenenPHP Data ObjectsDie PDO-KlassePDO::lastInsertId

Ein Service von Reinhard Neidl - Webprogrammierung.

Die PDO-Klasse

<<PDO::inTransaction

PDO::prepare>>

PDO::lastInsertId

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

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

Beschreibung

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

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.

Hinweis:

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.

Parameter-Liste

name

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

Rückgabewerte

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.


13 BenutzerBeiträge:
- Beiträge aktualisieren...
info at nospam dot timreeves dot de
11.12.2008 21:03
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;
?>
espen underscore g at mail dot com
25.10.2008 5:08
A better way of doing this in PostgreSQL is using the RETURNING keyword for the INSERT query.

You can use this SQL with the query function, and save a database call:

INSERT INTO tbl_users (firstname, lastname) VALUES ('John', 'Doe') RETURNING user_id;

This is also more accurate in an environment where more than one record might be inserted at the time, or other connections might insert records into the same table.
Alfred Reinold Baudisch
7.10.2008 2:21
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);

?>
Nour
15.08.2008 23: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
17.07.2008 17: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
26.05.2008 18: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.05.2008 19: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.05.2008 21:47
This function is not available for MSSQL either.
Jonathon Hibbard
28.04.2008 19:17
It should be noted here at this function will not display the correct ID if issuing ON DUPLICATE KEY UPDATE.

Example on a new Row:
<?php
$sql
= "INSERT INTO city (`city`) VALUES ('Paris') ON DUPLICATE KEY UPDATE `city` = 'Paris";
$dbh->query($sql);
echo
$dbh->lastInsertId();
?>

Above displays: 1
Expected display: 1

Example on an existing row that gets updated:
<?php
$sql
= "INSERT INTO city (`city`) VALUES ('Paris') ON DUPLICATE KEY UPDATE `city` = 'Paris";
$dbh->query($sql);
echo
$dbh->lastInsertId();
?>

Above displays: 2
Expected display: 1 (since no new records were inserted)

You'll have to work around this.
Yonatan Ben-Nes
17.05.2007 18: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.01.2007 11: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.08.2006 14: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.12.2005 15: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();
}
?>



PHP Powered Diese Seite bei php.net
The PHP manual text and comments are covered by the Creative Commons Attribution 3.0 License © the PHP Documentation Group - Impressum - mail("TO:Reinhard Neidl",...)