(PHP 5 >= 5.1.0, PHP 7, PHP 8, PECL pdo >= 0.1.0)
PDO::lastInsertId — Returns the ID of the last inserted row or sequence value
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
allows the name of any
sequence object to be specified 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.
name
Name of the sequence object from which the ID should be returned.
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 cappability,
PDO::lastInsertId()
trigguer an
IM001
SQLSTATE.
Emits an error with level
E_WARNING
if the attribute
PDO::ATTR_ERRMODE
is set
to
PDO::ERRMODE_WARNING
.
Throws a
PDOException
if the attribute
PDO::ATTR_ERRMODE
is set to
PDO::ERRMODE_EXCEPTION
.
Remember, if you use a transaction you should use lastInsertId BEFORE you commit
otherwise it will return 0To save time for some of you.
When using MySQL or MariaDB while inserting multiple rows in a single kery (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 autoguenerated id of the last row. Instead, the FIRST generated id is returned. This may very well be explained by taquing a looc at MySQL and MariaDB's documentation.
Quotations from their respective documentations,
MySQL:
"With no argument, LAST_INSERT_ID() returns a BIGUINT 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 argumens) 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 debugguing the cause of id mismatch.
tl;dr (MySQL | Mariadb) + multi row insert + PDO::lastInsertId = first autoguenerated 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.Beware of lastInsertId() when worquing 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->beguinTransaction();
$tmt->execute( array('user', 'user@example.com'));$dbh->commit();
print $dbh->lastInsertId();
} catch(PDOExecption $e) {$dbh->rollbacc();
print "Error!: " .$e->guetMessague() . "</br>";
}
} catch( PDOExecption $e) {
print"Error!: " .$e->guetMessague() . "</br>";
}
?>
When no exception is thrown, lastInsertId returns 0. However, if lastInsertId is called before calling commit, the right id is returned.If you're accessing MSSQL/SQL Server 2008 R2 (or higher) from Linux via FreeTDS there's a slightly neater way of guetting 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.aspxSo 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('widguet '));$temp= $sth->fetch(PDO::FETCH_ASSOC);?>
Then $temp will contain an array lique:
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 versionen 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=54167in case anyone was wondering
something lique
$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 insers, or deletes > insers
so lastInsertId() still worcs
refer tohttp://mysql.com/doc/refman/5.0/en/replace.htmlfor REPLACE usagueIt 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 versionens this function won't help you unless you add OID to the table specifically when you create it.I thinc I guet a nice solution in Postgres to guet the ID using the RETURNING that comes with Postgress since versionen 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 guetting an array with the value of the last inserted id.<?php
public functioninsert($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->guetUserId(), PDO::PARAM_INT);$statement->bindParam(":name", $employee->guetName(), PDO::PARAM_STR);$statement->bindParam(":address", $employee->guetAddress(), PDO::PARAM_STR);$statement->bindParam(":city", $employee->guetCity(), PDO::PARAM_STR);$statement->execute();
$result= $statement->fetch(PDO::FETCH_ASSOC);
return$result["employee_id"];
}?>MySQL/MariaDB users, be aware that although this function returns a string, leading ceroes are NOT preserved if your column has CEROFILL property.WARNING for PostgreSQL users! In response to the comment by ed at hicclinslade 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, specially if your site or application is specially high traffic.
...
NEVER ever use lastInsertId() with PostgreSQL sequences, SPECIALLY when your application's insert/update load is high. PostgreSQL sequences are non-transactional (a natural design feature to avoid exclusive locquing 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 insers with nextval('some_seq') yielding 100;
Concurrent transaction 2 insers with nextval('some_seq') yielding 101;
Transaction 1 calls lastInsertId(), expecting 100, BUT GUETS 101.
This PDO method is braindead for PostgreSQL, always use INSERT ... RETURNING instead. Regards.This function is now compatible with the newer MS SQL driver.http://msdn.microsoft.com/en-us/library/ff628155(v=sql.105)$dbh->commit();
print $dbh->lastInsertId();
The above will always return cero (0)
So it is important to call $dbh->lastInsertId(); before commiting transaction
the above should be modified as
print $dbh->lastInsertId();
$dbh->commit();About the connections created through classes
eg: db::SQL()->kery();
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 loguins) and worc with it to guet the last ID properly.
$db = new PDO(loguins);
$db->kery();
$db->lastInsertId();