By cburschka on

I am inserting rows into a custom table in Drupal. This table has an auto-incrementing row, and I need to find the generated ID of the record just inserted.

I'm a bit uncomfortable with calling db_query(), then mysql_insert_id(), because I'm effectively bypassing Drupal's database abstraction layer, and since I don't know exactly what goes on in db_query, I am making an assumption with this.

Is it safe to do this?

db_query("INSERT INTO {mytable} (column1,column2) VALUES(%d,%d)",$col1,$col2);
$id=mysql_insert_id();

Or is there a Drupal function that wraps this appropriately?

(Note that this is site-specific code which will never need to run on PostGre or otherwise.)

⋅ Categories: Drupal 5.x

Comments

AjK’s picture

AjK commented

Use abstraction not auto-incrementing fields.

$newId = db_next_id({mytable});
db_query("INSERT INTO {mytable} (id, column1,column2) VALUES(%d,%d,%d)",$newId,$col1,$col2);

Things may be changing in future Drupal releases (not sure on that) but the above is how it's currently done.

rszrama’s picture

rszrama commented

Yes, it's safe to do that for site-specific code.

Keep the code above w/ db_next_id() in mind for any contributed modules.

GS_Joris’s picture

GS_Joris commented

I was just wondering, on a very high traffic website, isn't it theoretically possible that between the db_next_id and the db_query that the ID has changed already? Especially if you're running loadbalanced websites. In that case mysql_insert_id() seems more reliable, since it's limited to the same connection.
I could be wrong though.

rszrama’s picture

rszrama commented

Good question. : ) I think you're safe here. db_next_id() returns the next ID in any sequence and increments it in the table so that no duplicate IDs should ever be served up regardless of how close the queries are together. Also, the function locks the sequences table when serving up an ID to prevent any sort of overlap there.

----------------------
Current Drupal project: http://www.ubercart.org

johnhanley’s picture

johnhanley commented

I've used db_next_id() many times in the past (for site-specific modules), but Drupal is already database-driven enough without hitting the database twice to retrieve and record a new row ID. Use auto increment and mysql_insert_id() and save yourself some cycles.

In general it amazes me how many Drupal developers have come to accept making 100's of database calls per page load is acceptable and "best practice."

dewolfe001’s picture

dewolfe001 commented

Amen!
What I love as well: that the db queries are supposed to be dbms neutral and abstracted, but it's common to find "type" and "timestamp" both of which are MySQL reserved words-- those are just the two I steer around daily. Some of the complexity and extra work is neccessary and really beneficial. Some of it, I shake my head at. This is one of the reasons I've been reticent to post modules and code fixes because they would run counter to how things are done in Drupal-- so I keep the fixes inside of my installs and make crib notes so that I can re-apply changes on code upgrades.

- Mike

wheelercreek’s picture

wheelercreek commented

In case anyone else is scratching their head trying to find this one, looks like it's now db_last_insert_id(). See

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

Tom Wheeler
http://www.wheelercreek.com

kate’s picture

kate commented

This helped me. I used db_last_insert_id() and it worked fine.

Thanks!

oskar_calvo’s picture

oskar_calvo commented

Hello Kate, db_last_insert_id() it's only for postgresql, or also works with mysql?

Thanks

Oskar

kate’s picture

kate commented

Hi Oskar,

It worked for me and I use mysql.

best of luck,
Kate

dwb17’s picture

dwb17 commented

While developing a module, it seems that Drupal changed the way I use to write SQL queries.
For example in-order to INSERT data into a table the following is what "Pro Drupal Development" recommends:

$table = 'orders';
  $record = new stdClass();
  $record->status = "preprocess";
  drupal_write_record($table,$record);
  return $record->bid;

So it seems that the use of mysql_insert_id() is no longer needed.

**Note the use of $record->bid is the same regardless of what the primary key is named.**Maybe someone can second this.