2

I have a problem with PDO::lastInsertId() method which doesn't return the id (primary key) of last inserted row, instead it returns another field which is a foreign key field.

PHP code:

$pdo = new PDO(...);
$stmt = $pdo->prepare($sql);
$stmt->bindParam(...);
$stmt->bindParam(...);
$stmt->execute();
$id = $pdo->lastInsertId();
// or
$id = $pdo->lastInsertId('services_id_seq'); // I think 'services_id_seq' is not necessary in MySQL
// both of them don't return the primary key of last inserted row

echo 'last inserted id: ' . $id;

MySQL Table structure:

...
id          int unsigned not null primary key auto_increment
customer_id int unsigned not null
user_id     int unsigned not null
....

inserted row in MySQL:

id    customer_id    user_id    ...
1     19             31         ...

PHP output:

last inserted id: 19

It should return 1 not 19. I don't know is anything wrong with my code or not.. or maybe this is the normal behavior :?

Return Value (PHP documentation):

  • 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.

Mustafa Shujaie
  • 806
  • 2
  • 10
  • 18
  • To get the serial is not that easy. http://stackoverflow.com/questions/2944297/postgresql-function-for-last-inserted-id#answer-2944335 – GreenRover Jan 14 '13 at 05:57
  • @GreenRover thanks, also it is postgresql :( I am using MySQL and I want to know is `PDO::lastInsertId()` the right way or not? or I did a mistake – Mustafa Shujaie Jan 14 '13 at 06:05
  • my fould i only red the sequenze and not thetag. For MySQL it looks right `$id = $pdo->lastInsertId();`. But check you insert perhaps it is invalid and you get the result of last insert. – GreenRover Jan 14 '13 at 06:08

2 Answers2

1

Run "SELECT LAST_INSERT_ID()" query.
If returned ID is still 19, not 2 (or whatever primary key should be after all tries), there is a problem with MySQL, irrelevant to PDO. You have to investigate it as a separate case (and, probably, separate question), supplying full SQL proof-code, able to run in console, involving creating a table, running insert and selecting LAST_INSERT_ID()

If this function returns the right value but PDO still wrong one - you have to probably bugreport it on bugs.php.net, again with full reproduceable code and all software names with exact version numbers provided.

Only one thing to make it clear: are you certainly sure that $sql variable in your question contains proper INSERT statement, not something like INSERT ON DUPLICATE or such? Or are there any triggers set on this INSERT?

Your Common Sense
  • 156,878
  • 40
  • 214
  • 345
  • it is: `$sql = 'INSERT INTO "services" ("customer_id", "user_id", ...) VALUES (:customer_id, :user_id, ...)';` other fields are just varchars and datetime type columns. and also there is no trigger set. I am checking your solution – Mustafa Shujaie Jan 14 '13 at 07:23
-1

I looked at PDO::lastInsertId() recently but found the description in the manual insufficiently convincing (or indeed comprehensible) and so opted for using something like this instead:

...
$stmt->execute();
$row = $stmt->fetch();
...

which seems to work for me.

I'm no PDO or MySQL expert, so this may not be quite right, but I also thought that the above approach would circumvent the possible problem of another row being inserted in the database (by a different user/thread) in between this thread's execute() and lastInsertID() calls.

If you're keen to keep lastInsertID() have you investigated whether, in your setup, the ID returned has to be an auto-increment field? The manual sort of hints that this should be so but is hopeless vague (IMHO).

Hope that's of some help.

Cheers, Ian.

Ian Lyon
  • 86
  • 6