db_last_insert_id: what it means and how to use it

Or: What's the right way to auto-increment sequences in Drupal?

Drupal 6, not Drupal 5

db_last_insert_id is not for getting the next available number in a sequence. Repeat, it is not for saying gee, I want to insert something into table foo, what was the last foo_id I put in? Then I'll add +1 to it. No!

The point of db_last_insert_id is that you let the database worry about what the next ID is. You use an auto_incremented (MySQL) or serial (PGSQL) column, and the INSERT statement inserts a number for that column for you.

Now you use db_last_insert_id('example_table_name', 'example_column_name') to get that number, that ID, if you need to use it in subsequent queries on other tables.

Definition of the function itself here:

http://api.drupal.org/api/function/db_last_insert_id

See for instance the use in the aggregator module:
http://api.drupal.org/api/file/modules/aggregator/aggregator.module/6/source

sequences
auto_increment and drupal

http://drupal.org/node/49836#comment-251682

db_last_insert_id

Returns the last insert id.
Parameters

$table The name of the table you inserted into.

$field The name of the autoincrement field.

http://api.drupal.org/api/function/db_last_insert_id/5
And 6: http://api.drupal.org/api/function/db_last_insert_id

It's not a complex function:

function db_last_insert_id($table, $field) {
  return db_result(db_query('SELECT LAST_INSERT_ID()'));
}

For CMT, made its own version of db_last_insert_id for use in Drupal 5.

made content_id on each table an auto_increment field (serial CHECK (content_id >= 0)) on PGSQL based on three hours re-reading the great serials/auto-increment debate and ultimately looking at core table declarations and confirming that db_last_insert_id is as simple as it looks

To look out for when researching this stuff:
The API confused me for a while: be careful not to search for an item in a non-head version of Drupal through the URL: even though your address ends in /5, you will really be looking at D6 or 7 now if the function does not exist in 5. Agaric did file an issue about this, but it's tricky even when you do know. And the other thing to know about API module for Drupal on http://api.drupal.org/api is that pgsql versions of db_ abstraction layer are not listed in the API. Agaric should file an issue about this, but please feel free to beat us to it.