4

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.

  • Perhaps having a timestamp column will make it easier to get last updated/inserted record. SELECT foo FROM bar ORDER BY timestamp DESC LIMIT 0,1 – GoodSp33d Jan 30 '13 at 7:30
  • No Need to add additional column. – Ravindra Singh Jan 30 '13 at 9:49
7

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();
}
  • @Ravindra I don't understand why you can't just use this one? You already know whether or not you're performing an Insert/Update based on the contents of $keyvalue, so just add a simple else { $last_id = $keyvalue; } to the above code and you're done :) – Clive Jan 30 '13 at 9:53
  • hey i tried this but not working.. – Ravindra Singh Jan 30 '13 at 10:12
  • SELECT LAST_INSERT_ID() is MySQL only, pretty much the same result as SELECT MAX(id) where id is the auto_increment/serial field – David Thomas Jan 30 '13 at 10:27
  • 3
    @Ravindra Have you come across drupal_write_record()? If you don't need the advanced functionality provided by db_merge() (conditional updates, etc) then you can just use that and save yourself the headache – Clive Jan 30 '13 at 10:31
  • 1
    @Clive you're right, but calling LAST_INSERT_ID() vs MAX(id) in the same function, directly after db_merge should give the same result afaik which I think is what OP is wanting to do there... Maybe some chance of race condition on the select from separate insert process, so perhaps last_insert_id() is ok there if mysql. – David Thomas Jan 30 '13 at 10:31
0

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.

  • David in case of db_merge() it is not mandatory that the Max id in the table is new record it may be older and updated on that time only. – Ravindra Singh Jan 30 '13 at 7:04
  • Just check the returned value from db_merge, if it was an insert you MUST query for the highest id value, if it was an update then no ID should be returned. – drcelus Jan 30 '13 at 7:11
  • if id does not insert any new records that time it fails. – Ravindra Singh Jan 30 '13 at 7:21
  • 2
    If an existing record was updated this would give incorrect result right ? – GoodSp33d Jan 30 '13 at 7:32
  • If you have DESC LIMIT 1 in your query, no need MAX(). – Sithu Jan 30 '13 at 7:39
0

Hey in Drupal 6 we can use

db_last_insert_id('table_name', 'name');

This gives the last inserted id.

-1

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();
  • 1
    Suppose i need to get last inserted id so please give me an example. – Ravindra Singh Jan 30 '13 at 7:14
  • We are already getting the last inserted by using the orderBy() method, If you send DESC and limit results to 1 – saadlulu Jan 30 '13 at 7:18
  • If it does not insert new record that time it only updates the values and may be last (Max) id of row will not affect – Ravindra Singh Jan 30 '13 at 7:22

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.