How to retrieve last MySQL INSERT ID from db_merge()

Question

I am using db_merge for inserting or updating new records in my table. I want to get the last inserted record id from this table, Thank you.

Answer 1

While there is RETURN_INSERT_ID which can be used with db_query(), I could not find a way to use with db_merge().

<?php
  $query = "INSERT INTO {table_name} (col1, col2) VALUES (:arg1, :arg2)";
  $args = array(':arg1' => $val1, ':arg2' => $val2);
  $last_id = db_query($query, $args, array('return' => Database::RETURN_INSERT_ID));
?

I think you will have to retrieve the last ID with another query, but you would need to check with MergeQuery::STATUS_INSERT.

$status = db_merge('example')
  ->key(array('name' => $name))
  ->fields(array(
      'field1' => $value1,
      'field2' => $value2,
  ))
  ->execute()

if( $status == MergeQuery::STATUS_INSERT ) {
      $last_id = db_query("SELECT LAST_INSERT_ID()")->fetchField();
}

Answer 2

I want to get the last inserted record id from this table...

You could do a second query, to get the latest inserted id:

$max_id = db_query("SELECT MAX(id) FROM {table}")->fetchField();

That will return the latest inserted id, but not the id of the latest updated.

Answer 3

Hey in Drupal 6 we can use

db_last_insert_id('table_name', 'name');

This gives the last inserted id.

Answer 4

You can use this.

      $node = db_select('table_name', 'f')
      ->fields('f', array('field_you_want'))
      ->condition('field', 'condition', 'expression')
      ->orderBy('id', 'DESC')
      ->range(0,1);
      ->execute()
      ->fetchAll();