PHP MySQL INSERT fails due to unique constraint

Question

On insert I am catching the unique constraint mysql_errno() 1062.

This works fine but I want to find the existing row to re-instate or modify it.

Is there are method to obtain the row id on insert fail? I tried mysql_insert_id() but realised that would only return the row I'm inserting (or failed to insert) therefore, I get 0.

Is there no option but to issue another mysql_query and simply perform a select on the duplicate value?

I just want to make sure there is no better, quicker, more economical way to do this.

Answer 1

You'd have to check the table for any "unique" keys (SHOW CREATE TABLE will list them all), and then query the table for the associated values in the insert query you'd attempted. So if you're inserting fields A,B,C,D and B,C have the unique key on them, then:

SELECT id, B, C FROM table WHERE B=$B and C=$C;

Answer 2

If you are attempting to insert a row if new or update existing values then REPLACE INTO is what you need. Also consider INSERT ... ON DUPLICATE KEY UPDATE Syntax if there are constraints involved as REPLACE INTO will DELETE and then INSERT.