db_affected_rows in Drupal 7 for db_query

Question

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.

Answer 1

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.

Answer 2

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();