How to retrieve last MySQL INSERT ID from Drupal 7 db_merge()

Drupal 7′s DBTNG database abstraction layer introduces the db_merge() command which replaces the if($id) { db_update() } else { db_insert() } construct. Very useful.

One issue I ran into is retrieving the last MySQL insert ID. db_insert() returns the ID as expected, but db_merge() only returns a STATUS_INSERT or STATUS_UPDATE integer value, which isn’t much good for anything.

Reading through Drupal 7′s source code, modules/simpletest/tests/database_test.test contained an elegant solution where we run a SELECT query using the known values to pull the row back out of the database, where the ID is correctly set.

$result = db_merge('test_people')
 ->key(array('job' => 'Presenter'))
 ->fields(array(
 'age' => 31,
 'name' => 'Tiffany',
 ))
 ->execute();
 $person = db_query('SELECT * FROM {test_people} WHERE job = :job', \
             array(':job' => 'Presenter'))->fetch();
 $last_insert_id = $person->id; // Core test does not actually include an ID,
                                // but this is how it could work

3 Comments

Filed under Code

3 Responses to How to retrieve last MySQL INSERT ID from Drupal 7 db_merge()

  1. Andrew Pelt

    You my acquaintance are a genius

  2. Tommy SD

    Love your blog, in fact arrived by checking yahoo and google for a comparable issue to this post. Which means this might be a late post nevertheless keep up the great work.

  3. The problem is with your solution, you can’t be 100% sure there’s no other INSERT before the SELECT.

Leave a Reply

Your email address will not be published. Required fields are marked *

*

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>