PDO lastInsertId () returns 0 when used when re-updating a key?

Question

I have a query like:

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

Then when I do this:

$insert_id = $a->lastInsertId()

If the query successfully inserted the row, it will return the insert identifier, as expected, if it updates the row, it will also return the updated row identifier, as expected (sort of). But if he did not contribute or update anything, because everything was the same, then it just returns 0.

I assume this is a pretty logical default behavior, but there is a way to change it so that it can return the id of the row that it was trying to update, for example, when it really changes something in the row.

Thanks.

Answer 1

There are many places where you can find the answer to this question, but the specificity of the PDO may hamper the search results ...

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

Additionally, you may need to specify an argument to a sequence object for ->lastInsertId() to make it work. I used to come across this problem in certain circumstances.

Answer 2

No, because there is no line that she is "trying" to update. Also, be careful: if you had a different insert request before this in the same session, you could get the LAST_INSERT_ID() value from this previous request.