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
You my acquaintance are a genius
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.
The problem is with your solution, you can’t be 100% sure there’s no other INSERT before the SELECT.