9

I want to get the record ID for the last inserted row in a database table. I am trying to use db_last_insert_id(), but it is throwing an exception.

Call to undefined function db_last_insert_id()

How do I get last inserted record ID?

15

In Drupal 6 you would use code similar to the following.

db_query("INSERT INTO {mytable} (intvar, stringvar, floatvar) VALUES (%d, '%s', %f)", 5, 'hello world', 3.14);
$id = db_last_insert_id('mytable', 'id_fieldname');

The Drupal 7 equivalent code is the following.

$id = db_insert('mytable')
  ->fields(array(
    'intvar' => 5,
    'stringvar' => 'hello world',
    'floatvar' => 3.14,
  ))
  ->execute();
  • I want newly inserted record id from a particular table from the db.Actually I need Newly Registered UserId(uid) in Rules how can i acheive it – prashanth May 13 '13 at 10:03
  • Do not forget to specify table name and field name in function db_last_insert_id($table, $field) – Vladislav Aug 25 '15 at 7:15
9

If you don't control the particular insert query yourself, you could always use a plain old SQL query:

$last_id = db_query('SELECT MAX(id_col) FROM {table}')->fetchField();
  • I tried using Max(uid) , but i am getting the record above the last record – prashanth May 13 '13 at 10:05
  • Then the record hasn't been inserted yet, so there's no new uid available – Clive May 13 '13 at 10:06
  • I am able to see the new record in the database but i am not getting the Id of that record – prashanth May 13 '13 at 10:07
  • Nope, that's impossible :) If you can see the uid in the database, logically selecting MAX(uid) must return that uid. If not your MySQL server is very very broken – Clive May 13 '13 at 10:10
  • That would be possible only if your function is called just before the last record is inserted. – Елин Й. Jan 2 '14 at 7:30
0
  $id = db_insert('mytable')
       ->fields(array(
       'intvar' => 5,
       'stringvar' => 'hello world',
       'floatvar' => 3.14,
       ))
       ->execute();

$id contains the last inserted id of the table. just do echo $id.

0

If fore some reason you need to get a fields value before inserting it this is a work around to do it with.

function _get_id($tableName, $fieldName) {

    $select = db_select($tableName, 'o');
    $fields = array(
        $fieldName,
    );
    $select->fields('o', $fields);
    $result = $select->orderBy($fieldName)->range(0,1)->execute()->fetchAll();
    return $result[0];
}

$lastId = _get_id('table_name' , 'uid');
0

A crude trick, but it works:

$myInsertID = db_query("INSERT INTO {fred} (fld1,fld2) VALUES ('a','b')", array(), array('return' => Database::RETURN_INSERT_ID));

or

$myInsertID = db_query("INSERT INTO {fred} (fld1,fld2) VALUES ('%s','%s')", array('a','b'), array('return' => Database::RETURN_INSERT_ID));

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.