Best way to find the last inserted ID in mysql using php

Question

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?

Voyeur

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. – Fornix

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! – Voyeur

-1 for asking the question instead of reading the manual – Hybris

@YourCommonSense well thats fair, thank you for clarifying the reason atleast. but this was also a question about performance of the codes. – Voyeur

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 – Hybris

@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.) – Fornix

@Fornix Yes I have noticed that, I have found a few good functions there but most of them are useless or inefficient. – Voyeur

Answer 1

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()(...)

Marjorie

Well that is my question, how would it react when there are a lot of consecutive sql connections and many sql queries running. – Voyeur

@AhouraGhotbi: LAST_INSERT_ID() is per connection. – Rhinencephalon

I think linking to the documentation would be better than a random forum post with no citations (dev.mysql.com/doc/refman/5.0/en/…). But, it is correct, and the mysql_pconnect note is a good note. – Fornix

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? – Voyeur

Answer 2

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.

Overwork

If you're going to copy/paste something from the documentation, you should cite it. – Fornix