PHP, PDO & MYSQL : lastInsertId() returns NULL

Question

I am rather new at PDO-based MySQL and I'm running into a problem.

This is the method I'm executing :

public function insert( $table, $data )
{
  // utility functions to auto-format the statements
  $keys = $this->getKeys($data);
  $placeholders = $this->getPlaceholders($data);

  $q = "INSERT INTO $table ($keys) VALUES ($placeholders)";

  // this simply returns a new PDO object
  $dbh = $this->createSession();

  $stmt = $dbh->prepare($q);
  $stmt->execute( array_values($data) );

  return $dbh->lastInsertId();
}

After that, I run my method and store the returned value in a variable :

$new_user_id = $U->insert( $data );

var_dump($new_user_id);

And I get

NULL

Note the query is actually executed, and my data is correctly inserted into my table; no problem on that side. It seems it just can't grab the last insert ID as I ask for it.

Thanks for your time.

try passing a couple of optional attributes to the PDO constructor, or call setAttribute with PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION — Elias Van Ootegem
– Elias Van Ootegem, Commented Apr 2, 2013 at 21:15
c/p from the other reply : I checked the table settings and there is an 'id' field, type INT, with A_I checked, Primary Key. — user2133586
– user2133586, Commented Apr 2, 2013 at 21:40
I had the same issue when I've tried to run insert on SQLite database that was read only. Solution is to check is_writable() at the beginning. — jcubic
– jcubic, Commented Dec 5, 2019 at 8:52

Aquatoad · Accepted Answer · 2013-04-02 21:21:20Z

1

Not sure about any PDO-specific issues, but by default MySQL only returns an insert id if there's an auto_increment integer field in the database (generally but not necessarily the primary key). If your table doesn't include this nothing is returned by $dbh->lastInsertId()

answered Apr 2, 2013 at 21:21

Aquatoad

7888 silver badges22 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

user2133586 Over a year ago

I checked the table settings and there is an 'id' field, type INT, with A_I checked, Primary. Still not sure it's not related to the DB though. But my config is pretty standard. Thanks for the reply.

user2133586 · Accepted Answer · 2013-04-03 13:24:40Z

0

I've reviewed the code again and I found that my value wasn't returned because of an intermediate method that wasn't passing the value correctly to the top-layer method.

Checking the value at the source shows no problem.

Thanks for the replies anyway.

answered Apr 3, 2013 at 13:24

user2133586

371 silver badge4 bronze badges

1 Comment

Andrew Larsson Over a year ago

Sadly, this was my issue, too.

Collectives™ on Stack Overflow

PHP, PDO & MYSQL : lastInsertId() returns NULL

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related