PDO lastInsertId() always return 0

Question

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 returns 0.

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, then crudQuery() method is called. As I mentioned before, I can add the data successfully to MySQL database. Unfortunately when I call lastInsterID() 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 (:

I notice the `static` keyword in your method definitions. *How* are you calling these methods? — Pekka, Aug 02 '12 at 11:53
Each table in database has a class (my framework creates them automatically if there is no class for a table). Each database table class extends to `databaseobjects.php`. This way, I don't repeat myself while coding. — Revenant, Aug 02 '12 at 11:58
Remembering that `lastInsertId()` is unique to the *connection*, not the *table* - are you executing any other DB queries between calling `create()` and `lastInsertID()`? Also note that `lastInsertId()` reports `0` if the queries are executed within a transaction and you query the id after `COMMIT` — DaveRandom, Aug 02 '12 at 12:11
I have double checked my queries and correct `INSERT` query ran just before running `lastInsertID()` method. I also tried `return $db->handler->lastInsertId('id');` without any success. — Revenant, Aug 02 '12 at 12:19

Gary G · Accepted Answer · 2012-08-02T12:44:58.340

22

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"

edited Aug 02 '12 at 12:44

answered Aug 02 '12 at 12:06

Gary G

5,014
2
23
18

yes it is included but as NULL so MySQL is assigning the ID value. column name of the primary key /auto_increment is called 'id' in all my database tables. I've just doubled checked SQL queries and correct `INSERT` is the last query which ran just before calling `lastInsertID()` method. – Revenant Aug 02 '12 at 12:16
I forgot to mention `NULL` values are not included in the query in my framework. It is just when I use `var_dump()` function I can see all columns of the related table (if no value is assigned it shows that column's value as `NULL`) – Revenant Aug 02 '12 at 12:22
I added sample code to test if this is a PDO problem or a problem with your application/framework. – Gary G Aug 02 '12 at 12:45
Thank you for your help. I did similar code sample to test PDO, still getting `0`. I will try to re-install XAMPP. However on my server my framework and code sample (mine and yours) works perfectly, – Revenant Aug 02 '12 at 14:30
Re-installing XAMPP did the trick. Thanks a lot for your time and help Gary G. – Revenant Aug 02 '12 at 14:54
It can also return 0 if an error occurred during the insert. – raphael75 Jun 10 '16 at 17:06

score 3 · Answer 2 · answered Oct 03 '17 at 13:50

3

After you commit a transaction PDO::lastInsertID() will return 0, so best to call this method before the transaction is committed.

answered Oct 03 '17 at 13:50

Harry Lewis

327
2
10

score 2 · Answer 3 · answered Jul 15 '18 at 14:34

2

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.

answered Jul 15 '18 at 14:34

slawkens

99
6

score 1 · Answer 4 · answered May 10 '18 at 16:12

1

When no exception is thrown, lastInsertId returns 0. However, if lastInsertId is called before calling commit, the right id is returned.

http://php.net/manual/es/pdo.lastinsertid.php

answered May 10 '18 at 16:12

Ivan Fretes

549
6
9

This is exactly what the issue was in my case. I solved it using your solution. Thanks for sharing! – Devner Feb 29 '20 at 15:32

score 0 · Answer 5 · answered May 03 '18 at 17:15

0

I got a 0 when the last insert statement failed due to a foreign key contraint. last_error was a string.

answered May 03 '18 at 17:15

Peter Ajtai

54,199
12
118
138

PDO lastInsertId() always return 0

5 Answers5

Linked

Related