PHP: mysql_insert_id

User Contributed Notes
mysql_insert_id

ed at is-cool dot net
08-May-2005 12:25


Beware, mysql_insert_id() only returns the value of the last syntaxically correct insert statement.



If your code has a problem and the insert is not understood by the server then the value of the last working insert command is returned.



Put something else in place such as "select count( id ) from table" before and after the mysql_insert_id() call to ensure that a row was inserted.

relic at daimi dot au dot dk
20-Feb-2005 08:57


A bit more on return values:

mysql_insert_id() returns 0 if you haven't actually manipulated anything yet.



Also, it returns 0 even if the DB connection is lost[0] between inserting and calling mysql_insert_id() - so you can always count on getting an integer.



[0] By 'lost' I mean e.g. a DB crash. Actually closing the open link and then trying to communicate with the DB will of course result in an error.

brodseba AT brodseba DOT com
15-Feb-2005 02:37


It's possible to do the same with M$ Server.



function odbc_insert_id()

{

  $query = "SELECT @@IDENTITY AS ID;";

  $result = odbc_exec($this->m_rConnectionID, $query);

  $row = odbc_fetch_object($result);

  return $row->ID;

}

sander [ad] deltaserv [d0t] nl
10-Nov-2004 07:38


In reply to: sly at noiretblanc dot org:



Make sure that auto_increment has an capital A as the first letter, otherwise it WON'T work! So you have to spell it as Auto_increment... And then it works fine.

Baak
01-Oct-2004 01:04


I believe the "resource link" being referred to is not what is returned from mysql_query() but the $link returned from mysql_connect(). mysql_insert_id() will just use the most recent connection if there is no explicit $link being used.



So the above example in the manual page itself should behave the same with mysql_insert_id($link) at the end instead of the mysql_insert_id() they used. If you had multiple connections, the $link might come in handy.



Also in reading the mysql manual itself, there is some enlightening information on the fact that this does appear to be totally safe to use because it is on a per-connection basis.



Here's the relevant quote from the manual on LAST_INSERT_ID() which is located here: http://dev.mysql.com/doc/mysql/en/Information_functions.html



"The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions."



Sounds safe to me. I couldn't imagine this would be done any other way *but* on a per-connection basis, otherwise chaos would ensue. The only way to test it would be to perform a multi-thread type test. Perhaps someone is up for it and wants to post their results somewhere? :)

Steve Bond
24-Jun-2004 01:47


If you use this function after doing an INSERT ... SELECT to insert multiple rows at once, you get the autonumber ID of the *first* row added by the INSERT.



e.g. if there are 4 records in table 'init' that have column 'type' = 2

I want to add these 4 records to table 'game'

Table game has an autonumber column 'game_id' that is currently at 32.



If I do this query:



INSERT INTO game (type, players, rounds)

SELECT type, players, rounds FROM init

WHERE type = 2



Then mysql_insert_id() will return 33, not 36.

Wayne Theisinger
26-Feb-2004 05:40


In response to treylane at example dot com.



It is very very very important that you put in an "or die" or some other form of error handling.



Some scripts can fail invisibly and insert invalid data throughout your whole database because of mysql_insert_id

inserting the last successful insertid rather than reporting that the last attempt failed.



example of an or die statement.



$result = mysql_query($sql)

or die("Invalid query: " . mysql_error());

$EventID = mysql_insert_id();

dtez
11-Feb-2004 06:36


any zerofills on your id get chopped off on this function because it returns an int.

sly at noiretblanc dot org
13-Nov-2003 05:29


To get the NEXT insert id use the mysql query SHOW TABLE STATUS LIKE 'tablename' and get the field auto_increment...

treylane at example dot com
24-Apr-2003 04:36


This might be obvious, but it tripped me up - be careful when using last_insert_id() with persistent connections - running last_insert_id() after a failed update/insert/etc will return the last insert id of the last successful update/insert made by that CONNECTION rather than 0 for the number of rows updated by the previous non-working query, and who knows what the last query run on that connection was.

jameszhou2001 at yahoo dot ca
16-Dec-2002 08:30


Just a reminder, mysql_insert_id() should be called after 'mysql_affected_rows()', but BEFORE 'mysql_query("COMMIT")'.

frumler at the-beach dot no_spam dot net
04-Aug-2001 01:17


If you want to use the ID that was generated for one table and insert it into a second table, you can use SQL statements like this: 





INSERT INTO foo (auto,text)


    VALUES(NULL,'text');              # generate ID by inserting NULL


INSERT INTO foo2 (id,text)


    VALUES(LAST_INSERT_ID(),'text');  # use ID in second table





...found here:


http://www.mysql.com/doc/en/Getting_unique_ID.html





It works even without inserting the NULL value for some reason ;)


The following is great for monitoring:


    $new_id = mysql_insert_id();


    print "New id: $new_id\n";





Hope it helps you all, cheers.

vksgeneric at hotmail dot com
09-Dec-1999 07:14


You can't do an INSERT DELAYED and expect to get anything but zero, for it runs in a separate thread, and mysql_insert_id() is tied to the current thread.


Vlad

mysql_insert_id

Description

Parameters

Return Values

Examples

Notes

See Also