PHP Doku:: Returns the auto generated id used in the last query - mysqli.insert-id.html

Verlauf / Chronik / History: (2) anzeigen

Sie sind hier:
Doku-StartseitePHP-HandbuchFunktionsreferenzDatenbankerweiterungenAnbieterspezifische DatenbankerweiterungenMySQL Improved ExtensionThe MySQLi classmysqli->insert_id -- mysqli_insert_id

Ein Service von Reinhard Neidl - Webprogrammierung.

The MySQLi class

<<mysqli::init -- mysqli_init

mysqli::kill -- mysqli_kill>>

mysqli->insert_id

mysqli_insert_id

(PHP 5)

mysqli->insert_id -- mysqli_insert_idReturns the auto generated id used in the last query

Beschreibung

Objektorientierter Stil

mixed $insert_id;

Prozeduraler Stil

mixed mysqli::mysqli_insert_id ( mysqli $link )

The mysqli_insert_id() function returns the ID generated by a query on a table with a column having the AUTO_INCREMENT attribute. If the last query wasn't an INSERT or UPDATE statement or if the modified table does not have a column with the AUTO_INCREMENT attribute, this function will return zero.

Hinweis:

Performing an INSERT or UPDATE statement using the LAST_INSERT_ID() function will also modify the value returned by the mysqli_insert_id() function.

Parameter-Liste

link

Nur bei prozeduralem Aufruf: Ein von mysqli_connect() oder mysqli_init() zurückgegebenes Verbindungsobjekt.

Rückgabewerte

The value of the AUTO_INCREMENT field that was updated by the previous query. Returns zero if there was no previous query on the connection or if the query did not update an AUTO_INCREMENT value.

Hinweis:

If the number is greater than maximal int value, mysqli_insert_id() will return a string.

Beispiele

Beispiel #1 mysqli->insert_id example

Objektorientierter Stil

<?php
$mysqli 
= new mysqli("localhost""my_user""my_password""world");

/* check connection */
if (mysqli_connect_errno()) {
    
printf("Connect failed: %s\n"mysqli_connect_error());
    exit();
}

$mysqli->query("CREATE TABLE myCity LIKE City");

$query "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
$mysqli->query($query);

printf ("New Record has id %d.\n"$mysqli->insert_id);

/* drop table */
$mysqli->query("DROP TABLE myCity");

/* close connection */
$mysqli->close();
?>

Prozeduraler Stil

<?php
$link 
mysqli_connect("localhost""my_user""my_password""world");

/* check connection */
if (mysqli_connect_errno()) {
    
printf("Connect failed: %s\n"mysqli_connect_error());
    exit();
}

mysqli_query($link"CREATE TABLE myCity LIKE City");

$query "INSERT INTO myCity VALUES (NULL, 'Stuttgart', 'DEU', 'Stuttgart', 617000)";
mysqli_query($link$query);

printf ("New Record has id %d.\n"mysqli_insert_id($link));

/* drop table */
mysqli_query($link"DROP TABLE myCity");

/* close connection */
mysqli_close($link);
?>

The above examples will output:

New Record has id 1.

6 BenutzerBeiträge:
- Beiträge aktualisieren...
fizzyorange at gmail dot com
1.04.2009 17:27
Note that if you Call a MySQL stored procedure to insert a new record and then reference $db->insert_id; you will get 0 back, not the last inserted ID.

It is therefore necessary to add a line to your MySQL Stored Procedure such as

   select last_insert_id() as intRecordKey;

after the insert so that the query will return the new key value.

Then in your php code you can use the following

<?php
 $objInsertResult
= $db->query($strSQL);
 
$objInsertRow = $objInsertResult->fetch_object();
 
$intRecordKey = $objInsertRow->intRecordKey;
?>

It would however be nice if $db->insert_id did return the last insert ID following a stored procedure call.
bert at nospam thinc dot nl
22.07.2008 17:58
Watch out for the oo-style use of $db->insert_id. When the insert_id exceeds 2^31 (2147483648) fetching the insert id renders a wrong, too large number. You better use the procedural mysqli_insert_id( $db ) instead.

[EDIT by danbrown AT php DOT net: This is another prime example of the limits of 32-bit signed integers.]
Nick Baicoianu
4.05.2007 10:10
When running extended inserts on a table with an AUTO_INCREMENT field, the value of mysqli_insert_id() will equal the value of the *first* row inserted, not the last, as you might expect.

<?
//mytable has an auto_increment field
$db->query("INSERT INTO mytable (field1,field2,field3) VALUES ('val1','val2','val3'),
('val1','val2','val3'),
('val1','val2','val3')"
);

echo
$db->insert_id; //will echo the id of the FIRST row inserted
?>
will at phpfever dot com
21.04.2006 3:40
I have received many statements that the insert_id property has a bug because it "works sometimes".  Keep in mind that when using the OOP approach, the actual instantiation of the mysqli class will hold the insert_id. 

The following code will return nothing.
<?php
$mysqli
= new mysqli('host','user','pass','db');
if (
$result = $mysqli->query("INSERT INTO t (field) VALUES ('value');")) {
   echo
'The ID is: '.$result->insert_id;
}
?>

This is because the insert_id property doesn't belong to the result, but rather the actual mysqli class.  This would work:

<?php
$mysqli
= new mysqli('host','user','pass','db');
if (
$result = $mysqli->query("INSERT INTO t (field) VALUES ('value');")) {
   echo
'The ID is: '.$mysqli->insert_id;
}
?>
matix.bg a gmail
14.03.2006 11:56
Some people are wondering how to get the ids of the rows inserted with loop. Here is it:

<?php
// stmt_init ...
for (;;;) {
 
// do some things...
 
$stmt->execute();
 
$ids[] = $mysqli->insert_id;
}
?>
alan at commondream dot net
3.11.2004 20:44
I was having problems with getting the inserted id, and did a bit of testing. It ended up that if you commit a transaction before getting the last inserted id, it returns 0 every time, but if you get the last inserted id before committing the transaction, you get the correct value.



PHP Powered Diese Seite bei php.net
The PHP manual text and comments are covered by the Creative Commons Attribution 3.0 License © the PHP Documentation Group - Impressum - mail("TO:Reinhard Neidl",...)