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.

Baggett

No other way but to SELECT first. – Sierra

Answer 1

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.

Hoelscher

Doesn't REPLACE INTO delete the existing row? I want to see the row and contents before overwriting, or at least give the user the option to modify it. – Baggett

It does - if you need to retrieve the row if it already exists then I can see no other option apart from using a SELECT statement. – Hoelscher

Answer 2

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;

Estrella