Get record id when mysql returns a recurring error

Question

Is there a way to get the record identifier (primary key) after insertion when the mysql error returns a duplicate key?

eg. How would I do this:

$sql = "INSERT INTO table (`col1`, `col2`) VALUES ('$val1', '$val2')"; $result = mysql_query($sql); if($result){ $id = mysql_insert_id(); } else { if(stristr(mysql_error(), "duplicate"){ $sql = "SELECT `id` FROM `table` WHERE `col1`='$val1' AND `col2`='$val2'"; $result = mysql_query($sql); $row = mysql_fetch_array($result); $id = $row['id']; } else { die(mysql_error()); } } 

Here I needed to make two sql-operators, which not only require time and effort, but also duplicate the code.

I cannot use ON DUPLICATE KEY UPDATE because I want to update another table using either the last inserted identifier or the identifier of a record that cannot be duplicated.

So, am I right in what I do? Or is there a way to get the row id?

thanks

Answer 1

MySQL will not tell you which record contains the original value, you will need to find out for yourself. Here are some suggestions:

• Finding the duplicate substring in the text of the error message does not look very reliable. You can simply check the value of mysql_errno () for code to duplicate the record, which is 1062 ( you can find all the codes in the manual ).

• The mysql extension does not provide a mechanism for determining the name of the broken key, so you will have to use an incompatible approach to parse the text of the error message:

   if( preg_match("/Duplicate entry '.*' for key '(.*)'/Ui", mysql_error(), $matches) ){ $violated_key = $matches[1]; }else{ throw new Exception('Could not find violated key name'); } 

• Alternatively, just run the previous query (there is no reason to avoid it):

   SELECT id FROM table WHERE col1=... AND col2=... FOR UPDATE 

  The FOR UPDATE clause blocks matching lines to avoid race conditions (assuming InnoDB).