mysqli::$insert_id

mysqli_insert_id

(PHP 5, PHP 7, PHP 8)

mysqli::$insert_id -- mysqli_insert_idDevuelve el id autogenerado que se utilizó en la última consulta

Descripción

Estilo orientado a objetos

Estilo por procedimientos

mysqli_insert_id(mysqli $link): mixed

La función mysqli_insert_id() devuelve el ID generado por una query (normalmente INSERT) en una tabla con una columna que tenga el atributo AUTO_INCREMENT. Si no se enviaron declaraciones INSERT o UPDATE a través de esta conexión, o si la tabla modificada no tiene una columna con el atributo AUTO_INCREMENT, esta función devolverá cero.

Nota:

Realizar una sentencia INSERT o UPDATE usando la función LAST_INSERT_ID() modificará el valor retornado por la función mysqli_insert_id().

Parámetros

link

Sólo estilo por procediminetos: Un identificador de enlace devuelto por mysqli_connect() o mysqli_init()

Valores devueltos

El valor de el campo AUTO_INCREMENT que fué actualizado por la consulta anterior. Devuelve cero si no hubo una consulta previa en la conexión o si la consulta no actualiza un valor AUTO_INCREMENT.

Nota:

Si el número es mayor que el valor máximo int, mysqli_insert_id() devolverá un string.

Ejemplos

Ejemplo #1 Ejemplo de $mysqli->insert_id

Estilo orientado a objetos

<?php
$mysqli
= new mysqli("localhost", "mi_usuario", "mi_password", "world");

/* comprobar la conexión. */
if (mysqli_connect_errno()) {
printf("Error de conexión: %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 ("Nuevo registro con el id %d.\n", $mysqli->insert_id);

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

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

Estilo por procedimientos

<?php
$link
= mysqli_connect("localhost", "mi_usuario", "mi_password", "world");

/* comprobar la conexión. */
if (mysqli_connect_errno()) {
printf("Error de conexión: %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 ("Nuevo registro con el id %d.\n", mysqli_insert_id($link));

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

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

El resultado de los ejemplos sería:

Nuevo registro con el id 1.
add a note add a note

User Contributed Notes 8 notes

up
45
will at phpfever dot com
17 years ago
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;
}
?>
up
1
mmulej at gmail dot com
3 years ago
There has been no examples with prepared statements yet.

```php
$u_name = "John Doe";
$u_email = "johndoe@example.com";

$stmt = $connection->prepare(
    "INSERT INTO users (name, email) VALUES (?, ?)"
);
$stmt->bind_param('ss', $u_name, $u_email);
$stmt->execute();

echo $stmt->insert_id;
```

For UPDATE you simply change query string and binding parameters accordingly, the rest stays the same.

Of course the table needs to have AUTOINCREMENT PRIMARY KEY.
up
1
www dot wesley at gmail dot com
4 years ago
When using "INSERT ... ON DUPLICATE KEY UPDATE `id` = LAST_INSERT_ID(`id`)", the AUTO_INCREMENT will increase in an InnoDB table, but not in a MyISAM table.
up
4
alan at commondream dot net
19 years ago
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.
up
3
bert at nospam thinc dot nl
15 years ago
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.]
up
1
Nick Baicoianu
16 years ago
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
?>
up
-5
owenzx at gmail dot com
10 years ago
The example is lack of insert_id in multi_query. Here is my example:
Assuming you have a new test_db in mysql like this:

create database if not exists test_db;
use test_db;
create table user_info (_id serial, name varchar(100) not null);
create table house_info (_id serial, address varchar(100) not null);

Then you run a php file like this:

<?php
define
('SERVER', '127.0.01');
define('MYSQL_USER', 'your_user_name');
define('MYSQL_PASSWORD', 'your_password');

$db = new mysqli(SERVER, MYSQL_USER, MYSQL_PASSWORD, "test_db", 3306);
if (
$db->connect_errno)
  echo
"create db failed, error is ", $db->connect_error;
else {
 
$sql = "insert into user_info "
   
. "(name) values "
   
. "('owen'), ('john'), ('lily')";
  if (!
$result = $db->query($sql))
    echo
"insert failed, error: ", $db->error;
  else
    echo
"last insert id in query is ", $db->insert_id, "\n";
 
$sql = "insert into user_info"
   
. "(name) values "
   
. "('jim');";
 
$sql .= "insert into house_info "
   
. "(address) values "
   
. "('shenyang')";
  if (!
$db->multi_query($sql))
    echo
"insert failed in multi_query, error: ", $db->error;
  else {
    echo
"last insert id in first multi_query is ", $db->insert_id, "\n";
    if (
$db->more_results() && $db->next_result())
      echo
"last insert id in second multi_query is ", $db->insert_id, "\n";
    else
      echo
"insert failed in multi_query, second query error is ", $db->error;
  }
 
$db->close();
}
?>

You will get output like this:

last insert id in query is 1
last insert id in first multi_query is 4
last insert id in second multi_query is 1

Conclusion:
1 insert_id works in multi_query
2 insert_id is the first id mysql has used if you have insert multi values
up
-7
drburnett at mail dot com
7 years ago
msqli_insert_id();
This seems to return that last id entered.
BUT,  if you have multiple users running the same code, depending on the server or processor I have seen it return the wrong id.

Test Case:
Two users added an item to their list.
I have had a few times where the id was the id from the other user.
This is very very rare and it only happens on my test server and not my main server.

I am guessing it is because of multicores (maybe hyperthreading) or how the operating system handles multi-threads.

It is rare, but it happens.
To Top