5

Im wondering as to what the best solution is to get the last inserted ID after a mysql inquiry?

I have found the following solutions : 

<?php
function get_current_insert_id($table)
{
    $q = "SELECT LAST_INSERT_ID() FROM $table"; 
    return mysql_num_rows(mysql_query($q)) + 1;
}
?>

or even using mysql_insert_id php function, but apparently this function will not work well with bigint (thats what I am using for ID field) and if there are alot of consecutive sql inquiries it could be unreliable.

Could someone provide a reliable and fast solution to achieve this task?

hakre
  • 193,403
  • 52
  • 435
  • 836
Ahoura Ghotbi
  • 2,866
  • 12
  • 36
  • 65
  • 1
    That's an invalid use of LAST_INSERT_ID() there. You would retrieve it like a normal column. mysql_num_rows() will always be one for that query. – Corbin Apr 29 '12 at 10:23
  • I found that function on php.net and the poster claimed that this function solves all the issues that are caused by `mysql_insert_id`! – Ahoura Ghotbi Apr 29 '12 at 10:25
  • 1
    -1 for asking the question instead of reading the manual – Your Common Sense Apr 29 '12 at 10:27
  • @YourCommonSense well thats fair, thank you for clarifying the reason atleast. but this was also a question about performance of the codes. – Ahoura Ghotbi Apr 29 '12 at 10:28
  • You should ask performance related questions only if you experience some performance related problems. otherwise they will make no sense and lead you to mistake – Your Common Sense Apr 29 '12 at 10:33
  • 1
    @AhouraGhotbi I've never seen a useful comment on the php.net docs. Usually they're full of crude work arounds written by people who don't properly understand the functions that the pages are discussing. The function you've posted will always return 2. (Unless the query fails, then it will return 1.) – Corbin Apr 29 '12 at 19:02
  • @Corbin Yes I have noticed that, I have found a few good functions there but most of them are useless or inefficient. – Ahoura Ghotbi Apr 29 '12 at 22:45

2 Answers2

10

Isn't SELECT LAST_INSERT_ID() reliable and safe enough?

From MySQL Doc: The ID that was generated is maintained in the server on a per-connection basis. This means that the value returned by the function to a given client is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that client. This value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own.

Note from a forum: (...)All bets are off, though, if for some reason you are using persistent connections, such as via mysql_pconnect()(...)

breiti
  • 1,145
  • 9
  • 17
  • Well that is my question, how would it react when there are a lot of consecutive sql connections and many sql queries running. – Ahoura Ghotbi Apr 29 '12 at 10:17
  • 1
    @AhouraGhotbi: [`LAST_INSERT_ID()`](http://dev.mysql.com/doc/refman/5.5/en/information-functions.html#function_last-insert-id) is per connection. – eggyal Apr 29 '12 at 10:19
  • I think linking to the documentation would be better than a random forum post with no citations (http://dev.mysql.com/doc/refman/5.0/en/information-functions.html#function_last-insert-id). But, it is correct, and the mysql_pconnect note is a good note. – Corbin Apr 29 '12 at 10:21
  • this is perfect, thank you so much for clarifying this for me! so `SELECT LAST_INSERT_ID() FROM $table` will be reliable for this right? and its the best performing code right? – Ahoura Ghotbi Apr 29 '12 at 10:27
4

If you insert multiple rows using a single INSERT statement, LAST_INSERT_ID() returns the value generated for the first inserted row only. The reason for this is to make it possible to reproduce easily the same INSERT statement against some other server.

Romil Kumar Jain
  • 20,239
  • 9
  • 63
  • 92