8

I just noticed that @Berdir was so nice to remove db_affected_rows from Drupal 7. I'm now wondering what the best practice is now to detect if the query you ran changed anything in the database.

A typical usecase would be to.

db_query(...);
if (!db_affected_rows()) {
  db_query(...);
}

I took a look at the query object returned from db_query, but it didn't seem like much help.

Update:
I see I was a bit unclear as to what circumstances I needed the info.

My current use case, is a quite simple one. I have a table for a node type with a nid column and some data columns. I have a form an upon submitting the form, I want to either insert or update the row in the db.

The problem with db_update/db_insert is that, if I use update first, and insert if update returns 0, I wont catch the condition, where the form was submitted with the value in the db. If I use db_insert first, that will raise en error if there already is a row in the db.

I suppose I in this specific condition could insert a blank value when the node is created and then only use update, but for some cases that might not be possible, if I needed to store info that was keyed to an external database. I would also like to avoid having to depend on database values for my code to function.

My usual strategy for such cases, has been to do a

db_query("INSERT IGNORE INTO ...")
if (!db_affected_rows()) {
  db_query("UPDATE ...");
}

Doing this is both simple and error free no matter what condition the db is in. The best option I can see right now, would be to handle it with SQL and do this:

db_query("INSERT ... ON DUPLICATE KEY UPDATE");

But I was hoping that the db API would be able to handle this.

9

That information is directly returned by the execute() method of Delete/UpdateQuery, see for example: UpdateQuery::execute().

<?php
$affected = db_update('some_table')
  ->fields(array(
    'some_field' => $value,
  ))
  ->condition('another_field', $id)
  ->execute();
?>

And InsertQuery::execute() returns the last insert id.

8

After digging around, I found that Drupal supplies a ready named tool for my exact use case:

Insert a row in the db, or update the existing if it's already there.

This is called merge queries, which can be done atomically for some db engines.

The syntext is pretty simple:

db_merge('example')
  ->key(array('name' => $name))
  ->fields(array(
    'field1' => $value1,
    'field2' => $value2,
))
->execute();
  • Ah yes, that is the correct answer for your updated question :) Note that merge queries have been redesigned late in the D7 development cycle to actually work like MERGE sql queries, which are part of the SQL 2003 standard but no dbms actually implements it yet, so all dbms require two queries (they are made atomic by using transactions). The problem with the single query approach which was used for MySQL was that it completely ignored the key() definition and simply worked with the unique/primary keys of a given table. – Berdir Mar 25 '11 at 21:50
  • @Berdir: Thanks for pointing me in the right direction. I'm one of those developers who like to write SQL and have a hard time getting used to the new db API :) – googletorp Mar 25 '11 at 21:53
  • Thanks for this pointer. However, I had to resort to db_query anyways as the Drupal db api does not allow using constants like CURRENT_TIMESTAMP (see drupal.org/node/215821) – Whiskey Dec 5 '11 at 16:05

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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