PDO lastInsertId() always return 0
Solution 1
Other than a bug in php/PDO or your framework, there are two possibilities. Either lastInsertId()
is called on a different MySQL connection than the insert, or you are generating the id in your application/framework and inserting it, rather than letting auto_increment generate it for you. Which column in the table is the primary key/auto_increment? Is that column included in $attributes
in your create()
function?
You can test PDO to make sure that part is working correctly with this code (in a new file):
// Replace the database connection information, username and password with your own.
$conn = new PDO('mysql:dbname=test;host=127.0.0.1', 'user', 'password');
$conn->exec('CREATE TABLE testIncrement ' .
'(id INT NOT NULL AUTO_INCREMENT PRIMARY KEY, name VARCHAR(50))');
$sth = $conn->prepare('INSERT INTO testIncrement (name) VALUES (:name)');
$sth->execute([':name' => 'foo']);
var_dump($conn->lastInsertId());
$conn->exec('DROP TABLE testIncrement');
When I ran this script, the output was
string(1) "1"
Solution 2
After you commit a transaction PDO::lastInsertID() will return 0, so best to call this method before the transaction is committed.
Solution 3
The one other problem could be using $pdo->exec($sql) instead of $pdo->query($sql).
exec($sql) will return always 0 when you use $pdo->lastInsertId(). So use query() instead.
Solution 4
When no exception is thrown, lastInsertId returns 0. However, if lastInsertId is called before calling commit, the right id is returned.
Revenant
Updated on July 09, 2022Comments
-
Revenant almost 2 years
I've come across with a problem. My framework was working just fine with PHP 5.3.0. I upgraded my PHP version to PHP 5.4.x and I started to have few issues with some parts of my framework.
After PHP version upgrade,
PDO lastInsterId()
always returns0
.I have auto-increment field called
id
. It is adding the data to database without any problems.For some reason I keep getting 0 as last insert id.
Here is my code;
databaseobjects.php
public static function create () { global $db; $attributes = self::sanitize(static::$fields); $sql = "INSERT INTO ".PREFIX.static::$table_name." ("; $sql .= join(", ", array_keys($attributes)); $sql .= ") VALUE (:"; $sql .= join(", :", array_keys($attributes)); $sql .= ")"; return ($db->crudQuery($sql, $attributes)) ? true : false; } public static function lastInsertID () { global $db; return $db->handler->lastInsertId(); }
database.php
public function crudQuery($sql, $data) { $sth = $this->handler->prepare($sql); return $sth->execute($data); }
First
create()
method is called, thencrudQuery()
method is called. As I mentioned before, I can add the data successfully to MySQL database. Unfortunately when I calllastInsterID()
method, it always returns 0.I will be really glad if you can help me out with this problem before I will get the last ID with SQL Query (: