1

Is it possible to run the following query with SelectQuery ?

SELECT LAST_INSERT_ID();

I would do this with db_query(), but I'm finding a way to use the query builder.

$last_id = db_query("SELECT LAST_INSERT_ID()")->fetchField();
9
  • Why can't you use PDO, cause execute function in PDO will return you the inserted ID? Jan 30, 2013 at 8:36
  • @Mathankumar, What do you mean about PDO? AFAIK, PDO is extension used by the Drupal database layer.
    – Sithu
    Jan 30, 2013 at 9:05
  • 2
    Is your computer broken?! Can you not test to see if it works yourself? Drupal Answers isn't a code debugger/IDE after all :)
    – Clive
    Jan 30, 2013 at 10:09
  • @Clive, Somebody might have tried this. I will submit an answer after I tested. Have you thought of it or tried it? Sometimes, I wrote codes for others in Drupal Answers because I believe it builds up my Drupal skill :)
    – Sithu
    Jan 30, 2013 at 10:17
  • 2
    @Clive, Ok, then. At least, you have known it :) I asked this for the sake of future visitors.
    – Sithu
    Jan 30, 2013 at 10:22

2 Answers 2

0

If you need the last insert ID, after you executed an INSERT with the InsertQuery class, InsertQuery::execute() returns that value.

The last insert ID of the query, if one exists. If the query was given multiple sets of values to insert, the return value is undefined. If no fields are specified, this method will do nothing and return NULL. That makes it safe to use in multi-insert loops.

Differently, UpdateQuery::execute() returns the number of affected rows.

4
  • Thank you, kiamlaluno. This question is somehow related to the other question which uses db_merge(), not db_insert().
    – Sithu
    Jan 30, 2013 at 16:40
  • I just used db_insert() for testing purpose only.
    – Sithu
    Jan 30, 2013 at 16:46
  • The answer is still correct: After executing an INSERT with db_insert(), there is no need to execute a new query to get the value of the last insert ID.
    – apaderno
    Jan 30, 2013 at 16:54
  • The question is intended for "How to use db_select() for SELECT LAST_INSERT_ID(). It is not for "How to get the last insert ID". Please assume that the insert operation just took place in another hook and I cannot get the value of the last insert ID in my hook. Anyway, I really appreciate your answer. I did not mean yours is not correct. Thank you. I just updated my question and answer to make this more clear. I was also curious to know how to write such kind of odd queries with db_select().
    – Sithu
    Jan 31, 2013 at 3:12
0

Although the original query has no table name, db_select() would expect the table name.
The following works perfectly assuming the insert operation took place in another hook and the last insert id is not available into my hook:

$query = db_select('mytable');
$query->addExpression('LAST_INSERT_ID()', 'last_id');

$result = $query->execute()->fetch();
print $query;
echo '<br>';
echo 'Insert ID: '.$result->last_id;

Query output:

SELECT LAST_INSERT_ID() AS last_id FROM {mytable} mytable

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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