PDO lastInsertId() returns 0 when using on duplicate key update?

Question

I've got a query like the following:

$a = $members->prepare("insert into a(name) values(:name) 
                            on duplicate key update name = :name");

Then when I do:

$insert_id = $a->lastInsertId()

If the query successfully inserted a row, it will return the insert id as expected, if it updated a row, it will also return the updated row id as expected (sort of). But if it neither inserted or updated anything because everything was the same, then it just returns 0.

I guess this is pretty logical default behaviour, but is there a way to change it so that it can return the id of the row it attempted to update, like it does when it actually changes something in the row.

Thanks.

Answer 1

There are many places to find the answer on this, but the specificity to PDO may be hindering your search results...

First, make sure to add id=LAST_INSERT_ID(id) to the ON DUPLICATE clause, as noted at the bottom of the DOCS. The id in this case is the column name of your primary key (so in your case it may/may not be titled id).

Also, you may have to specify a sequence object argument to ->lastInsertId() to make it work. I've encountered that problem in certain circumstances before.

Answer 2

No, because there is no row it 'attempts' to update. Also, be careful: if you had another insert query before that in the same session, you could get value of LAST_INSERT_ID() from that previous query.