mysql_list_dbs

mysql_info

Last updated: Fri, 18 May 2012

mysql_insert_id

(PHP 4, PHP 5)

mysql_insert_id — Liefert die ID, die in der vorherigen Abfrage erzeugt wurde

Beschreibung

int mysql_insert_id ([ resource $Verbindungs-Kennung ] )

Liefert die ID, die für eine AUTO_INCREMENT Spalte durch die vorherige Abfrage (meist INSERT) erzeugt wurde.

Parameter-Liste

Verbindungs-Kennung: Die MySQL-Verbindung. Wird die Verbindungskennung nicht angegeben, wird die letzte durch mysql_connect() geöffnete Verbindung angenommen. Falls keine solche Verbindung gefunden wird, wird versucht, eine Verbindung aufzubauen, wie es beim Aufruf von mysql_connect() ohne Angabe von Argumenten der Fall wäre. Falls zufällig keine Verbindung gefunden oder aufgebaut werden kann, wird eine Warnung der Stufe E_WARNING erzeugt.

Rückgabewerte

Die ID, die für eine AUTO_INCREMENT Spalte durch die vorherige Abfrage bei Erfolg, 0 falls die vorherige Abfrage keinen AUTO_INCREMENT Wert erzeugt hatte oder FALSE falls keine MySQL verbindung existierte.

Beispiele

Beispiel #1 mysql_insert_id() Beispiel


<?php
$link = mysql_connect('localhost', 'mysql_user', 'mysql_password');
if (!$link) {
    die('Keine Verbindung möglich: ' . mysql_error());
}
mysql_select_db('mydb');

mysql_query("INSERT INTO mytable (product) values ('kossu')");
printf("Letzer eingefügter Datensatz hat id %d\n", mysql_insert_id());
?>

Anmerkungen

Achtung

mysql_insert_id() konvertiert den Typ der Rückgabe der nativen MySQL C API Funktion mysql_insert_id() in den Typ long (als int in PHP bezeichnet). Falls Ihre AUTO_INCREMENT Spalte vom Typ BIGINT (64 Bit) ist, ist der Wert den mysql_insert_id() liefert, nicht korrekt. Verwenden Sie in diesem Fall stattdessen die MySQL interne SQL Funktion LAST_INSERT_ID() in einer SQL-Abfrage. Für weitergehende Informationen bezüglich PHPs maximaler Integer Werte, lesen sie bitte integer Dokumenation.

Hinweis:
Da mysql_insert_id() mit der zuletzt ausgeführten Abfrage arbeite, stellen sie sicher, dass sie mysql_insert_id() direkt nach der Abfrage, die den Wert erzeugt aufrufen.

Hinweis:
Der Wert der MySQL SQL Funktion LAST_INSERT_ID() liefert immer den zuletzt erzeugten AUTO_INCREMENT Wert. Dieser wird zwischen Abfragen nicht zurückgesetzt.

Siehe auch

mysql_query() - Sendet eine Anfrage an MySQL
mysql_info() - Liefert Informationen über die zuletzt ausgeführte Anfrage zurück

add a note User Contributed Notes
mysql_insert_id

daiguo
05-Jul-2007 03:43


To: Ben AT alfreido.com



<?php

function previous_id($table, $id_column) {

  if ($table && $id_column) {

    $result = mysql_query("SELECT MAX(".$id_column.") AS maxid FROM ".$table);

    $stuff = mysql_fetch_assoc($result);

    return $stuff['maxid'];

  } else {

    return false;

  }

}

?>



Use MySQL's built-in function MAX(). It's faster because any min/max algorithm is faster than sorting entire table.

Ben AT alfreido.com
14-Jun-2007 05:58


I realise this isn't the best way of doing it, but if use auto_increment in your tables, which just increases by one every time you add a row, you can get the last used id simply by quering the database, selecting the id column and ordering it descendingly and pulling out the first row returned. The added bonus is you can do it anytime, instead of just after an INSERT query.



<?php

function previous_id($table, $id_column) {

  if ($table && $id_column) {

    $result = mysql_query("SELECT " . $id_column " FROM " . $table . "

    ORDER BY " . $id_column . " DESC

    LIMIT 1");

    $stuff = mysql_fetch_assoc($result);

    return $stuff[$id_column];

  } else {

    return false;

  }

}

?>



Just change $table to the table name, and $id_column to the column which auto increments.

Typer85 at gmail dot com
25-Dec-2006 07:44


Please be warned that this function, like almost every other MySQL function, requires that the same database resource link that was used to insert the data into the database be used to get the last auto increment value.



If you close the database connection after inserting data, then open one up again and then attempt to get the last auto increment value, you will get an incorrect value since all resources related to the last insert query were cleaned up after the connection was closed.

Bob M.
16-Nov-2006 07:41


There is a problem with the function below submitted by rudolflai



In that mysql_autoid function, what happens when between the time you've retrieved the id and the time you do something with it (like INSERT a new record) another new record has been inserted into the database in the interim?



Better make sure you LOCK the tables you're working on before you use the function and don't UNLOCK until after you've done something with the id returned.

buana95 att yahoo dott com
29-Oct-2006 10:57


It's not true that mysql_insert_id() only returns the ID generated for an AUTO_INCREMENT column by the previous INSERT query.



We can use LAST_INSERT_ID() statement that will return value for mysql_insert_id().



Example:



$sql = "UPDATE `mytable` SET `mytable_id`= LAST_INSERT_ID(`mytable_id` + 1) WHERE ...;



@mysql_query($sql);

$last_id = mysql_insert_id();



LAST_INSERT_ID() statement will affect mysql_insert_id().



Regards,

Buana

mariano at uvcms dot com
19-Sep-2006 06:01


When used in transactions, mysql_insert_id() MUST be called before committing. Otherwise, it will return unpredictable results.

squezy16 at hotmail dot com
17-Sep-2006 05:46


One of the problem of the LAST_INSERT_ID function is that it may return an old Auto_Increment number if the INSERT did not have the time to run properly while the mysql_insert_id always returns the good value. I tried to insert a row imidiatly followed by a select LAST_INSERT_ID and it kept on returning me the LAST_INSERT_ID before my insert statement.

rudolflai at gmail dot com
11-Sep-2006 11:59


Hi,



Below is a snippet for retriving the next insert id from a mysql table. It functions like the auto_increment option in phpMyAdmin. Hope you find it helpful. =]



<?php 



function mysql_autoid($id,$table){

    $query = 'SELECT MAX('.$id.') AS last_id FROM '.$table;

    $result = mysql_query($query);

    $result = mysql_fetch_array($result);

    return $result[last_id]+1; 

}



/// usage mysql_autoid('nID','news');



?> 



Cheers,

Rudolf

james dot baxter at nospamplease dot com
09-May-2006 02:09


Have had many people coming to me with issues regarding last_insert_id and mysql_insert_id() etc



This is a very simple solution that caters for most. As you can see it gets the last auto inc from the field, in this case, ID.



mysql_query("LOCK TABLES art WRITE");



mysql_query("INSERT INTO table (....) VALUES (....)");



$result = mysql_query("SELECT MAX(ID) AS LAST_ID FROM table");



$result = mysql_fetch_array($result);



mysql_query("UNLOCK TABLES");



echo "Last ID#" . $result[LAST_ID];

john k.
31-Jan-2006 11:55


Be careful when using "insert ignore". If the unique index already exists, the record will not be added, but it WILL give you the id of the next auto_increment value even though it didn't create it.



<?php

$sql = "insert ignore into sometable set num=10";

mysql_query($sql) or die();

echo mysql_affected_rows()."<br>";

echo mysql_insert_id()."<br><br>";



// same record, database is unique on 'num'

$sql = "insert ignore into sometable set num=10";

mysql_query($sql) or die();

echo mysql_affected_rows()."<br>";

echo mysql_insert_id()."<br><br>";

?>



would give:

1

116372



0

116373

heiligkind at yahoo dot de
14-Dec-2005 10:31


If you insert a data row by using the ON DUPLICATE KEY UPDATE clause in an INSERT-statement, the mysql_insert_id() function will return not the same results as if you directly use LAST_INSERT_ID() in MySQL.



See the following example:



<?

   // insert a datarow, primary key is auto_increment

   // value is a unique key

   $query = "INSERT INTO test (value) VALUES ('test')";

   mysql_query( $query );



   echo 'LAST_INSERT_ID: ',

          mysql_query( "SELECT LAST_INSERT_ID()" ),

          '<br>mysql_insert_id: ',

          mysql_insert_id();



?>



This will print:



LAST_INSERT_ID: 1

mysql_insert_id: 1



In this case the function returns the same as the MySQL-Statement.

But see the insert on an existing key:



<?

   $query = "INSERT INTO test (value) 

                  VALUES ('test')

                  ON DUPLICATE KEY UPDATE value = 'test2'";

   mysql_query( $query );



   echo 'LAST_INSERT_ID: ',

          mysql_query( "SELECT LAST_INSERT_ID()" ),

          '<br>mysql_insert_id: ',

          mysql_insert_id();



?>



This will print:



LAST_INSERT_ID: 2

mysql_insert_id: 1



By using the ON DUPLICATE KEY UPDATE clause, only the old datarow will be modified, if the INSERT statement causes a duplicate entry, but the LAST_INSERT_ID() function returns the next auto_increment value for the primary key, which is by the way not set as the next auto_increment value in the database.



The mysql_insert_id() function returns the primary key of the old (and changed) data row. For me this is the right operation method, because the LAST_INSERT_ID() function returns a value which is not referenced to a data row at all.



Greets from Munich.



heiligkind

Soroushl at gmail dot com
07-Dec-2005 06:53


As mentioned by frumler at the-beach dot no_spam dot net



the LAST_INSERT_ID works like a charm when inserting values into tables.



I'm not sure why anyone would need mysql_insert_id() when LAST_INSERT_ID is readily available.



example:



Say you have a table called "transaction" and a table called "accounts".  Obviously each account must be created using a transaction, so every time a record is created in the accounts table, a record must be created in the transaction table containing the same account_id(auto_increment) that was just created by mysql.



Here's a simple way to do this:



<?php

$account_query="INSERT INTO accounts (account_id,type) VALUES (NULL,'saving')";

$transaction_query="INSERT INTO transactions(transaction_id,account_id) VALUES (NULL,LAST_INSERT_ID)";



$a_query=mysql_query($account_query);

$t_query=mysql_query($transaction_query);



?>

02-Dec-2005 07:48


Take care of setting an empty value for the AUTO_INCREMENT Field else you never get a value except zero returned from mysq_insert_id() ....



Ciao Ephraim

bholbrook at servillian dot com
01-Sep-2005 10:50


My apologies for the error below (that was modified out of a class) - as you cannot define a constant as an array.



replace the line:



<?



define("ID",...);



?>



with



<?



$mysql_id = mysql_query("...");



?>



$mysql_id is now an array in which the first element $mysql_id[0] holds the last inserted id.



Apologies if anyone struggled over that one... esp. the noobs.

bholbrook at servillian dot com
30-Aug-2005 10:45


An effective way to retrieve the last insert id without fail or having two queries inserted at the same time causing an unexpected number being returned.



<?



mysql_query("LOCK TABLES apc_forms WRITE");

mysql_query("SET AUTOCOMMIT = 0");

mysql_query("INSERT INTO apc_forms (form_title, form_event_id, form_expirey) VALUES ('title',1,'2005-10-10')");

define('ID',mysql_query("SELECT LAST_INSERT_ID()"));

mysql_query("COMMIT");

mysql_query("UNLOCK TABLES");



?>

athies at gmail dot com
21-Jul-2005 11:36


Just a quick note. mysql_insert_id() does work with REPLACE.

ed at is-cool dot net
08-May-2005 07: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 03: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 09: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
11-Nov-2004 02: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 08: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 08: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 12: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
12-Feb-2004 01:36


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

sly at noiretblanc dot org
14-Nov-2003 12: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 11: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
17-Dec-2002 03: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 08: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
10-Dec-1999 02: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

add a note

mysql_list_dbs

mysql_info

Last updated: Fri, 18 May 2012