9

Is anyone know how to set 'INSERT IGNORE' in db_insert?

I cannot use db_merge because I'm going to insert many rows.

$query = db_insert('table')->fields(array('foo', 'bar'));
foreach ($rows as $row) {
  $query->values(array(
    'foo' => $row->foo,
    'bar' => $row->bar,
  ));
}
$query->execute();

2 Answers 2

5

You've got two options:

  1. Subclass InsertQuery and add that functionality in yourself (it's currently not implemented)
  2. Use db_query() to run a raw SQL string instead.

The only mention of INSERT IGNORE in the Drupal 7 codebase is in DatabaseConnection_mysql:nextId; it's not actually used, but in its place is a raw query string containing ON DUPLICATE KEY UPDATE, that gets passed directly to db_query().

I may be wrong, but I'd assume that's as good an indication as any that a raw SQL string along with db_query() is the way to go.

4

Although it's not possible to use "INSERT IGNORE", there is a way you can get around this in PHP. Something like this:

try {
    $insertID = db_insert('crawl_data')->fields(array(
        'url' => $url, 
    ))->execute();
} catch (Exception $ex) {

}

In this example I have a database of "urls". But i dont want to first do a check if the item is in the database. I want it to happen in one go. So this is sort of the same behaviour as "insert ignore".

However, it's generally bad to have this:

catch (Exception $ex) {
    // open space
}

So it might be better to do this:

catch (Exception $ex) {
    $error = $ex->getMessage();
    if (strpos($error, 'SQLSTATE[23000]: Integrity constraint violation') !== false) {
        // then we know it's an error we can ignore
    }
    else {
        // just throw the error again
        throw $e;
    } 
}

It's either this or doing two database queries.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.