Get ID of record when mysql returns duplicate error

Question

Is there a way to retrieve the ID of a record (primary key) after an insert when the mysql error returns a duplicate key?

E.G. How I would go about it:

$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've had to do two sql statements which not only take time and effort, but duplicate code as well.

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

So, am I right in what I'm doing? Or is there a way to get the id of the row?

Thanks

Answer 1

MySQL will not tell you which record holds the original value, you'll have to find out yourself. Here you are some tips:

• Looking for the duplicate substring in the text of the error message does not look very robust. You can just test the value of mysql_errno() against the code for duplicate entry, which is 1062 (you can find all codes in the manual).

• The mysql extension does not provide a mechanism to find out name of the violated key, so you'll have to use the non-robust approach of parsing 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 a previous query (there's no reason to avoid it):

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

  The FOR UPDATE clause will lock matching rows to avoid race conditions (assuming InnoDB).