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(); You've got two options:
InsertQuery and add that functionality in yourself (it's currently not implemented)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.
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.
