Drupal 7 db_insert() execute() error when table contains unique keys

Question

I have a database (mysql) table that contains a unique key in barcode column.

What I intend is this:

• I have a form that has a barcode field.
• When user submits the form and the barcode entered already exists in the database table, I should just give an error message.
• Currently, it gives me PDOException: ... Duplicate entry and the page contents does not load.

This is how I did the insert:

$insert = db_insert('mymodule_mytable')->fields($fieldarray)->execute();

At that line, Drupal 7 returns PDO error so I cannot execute the lines after it. Thus, I cannot also check the return value in $insert.

Is there workaround to avoid the PDO error? I need to do something like:

if($insert === FALSE) {
  drupal_set_message(t('Error'), 'error');
}

Answer 1

PHP has exception handling in the form of try...catch blocks:

try {
  $insert = db_insert('mymodule_mytable')->fields($fieldarray)->execute();
}
catch (PDOException $e) {
  drupal_set_message(t('Error: %message', array('%message' => $e->getMessage())), 'error');
}

So rather than ignore the error completely, you can actually respond to different types of exception and react accordingly.

Answer 2

I'm not sure if one should rely on exception handling here as the accepted answer suggests. Instead of introducing a try...catch block, a better approach could be to explicitly check if the record with the ID already exists before inserting it:

if (db_query("SELECT 1 FROM {mymodule_mytable} WHERE mid = :mid", array(':mid' => $mid))->fetchField()) {
  drupal_set_message(t('ID already exists!'), 'error');
}
else {
  // Perform the insertion.
}

Edit: As mentioned by kiamlaluno in the comments, the added advantage of this approach is that checking for a duplicate ID can take place separately in the form validation handler, where the user input is supposed to be checked.