mysqli_insert_id() - ID from AUTO

PHP Modules Tutorials - Herong's Tutorial Examples

∟mysqli_insert_id() - ID from AUTO_INCREMENT

This section provides a tutorial example on how to retrieve the ID value generated for the AUTO_INCREMENT column of the last query by calling the mysqli_insert_id() function.

If you are inserting rows into a table with IDs generated from an AUTO_INCREMENT column, you can retrieve the ID value generated for the AUTO_INCREMENT column of the last query using the mysqli_insert_id() function:

$id = mysqli_insert_id($con);

where:
  $con - The MySQL connection object
  $id - The first ID for AUTO_INCREMENT column of the last query

Here is a PHP script example showing you how to use the mysqli_affected_rows() function:

%lt;?php
#  mysqli-insert-id.php
#- Copyright 2009-2026 (c) HerongYang.com. All Rights Reserved.

  $con = mysqli_connect("127.0.0.1", "herong", "TopSecret", 
    "MyBase");
  $res = mysqli_query($con, "DROP TABLE MyTable");
  $res = mysqli_query($con, 
    "CREATE TABLE MyTable ".
    "(ID INTEGER AUTO_INCREMENT, Value INTEGER, PRIMARY KEY (ID))");

  # insert a row with given ID 
  $num = mysqli_query_id($con, 
    "INSERT INTO MyTable (ID, Value) VALUES (9, FLOOR(RAND()*300))");

  # insert a row with auto-generated ID 
  $num = mysqli_query_id($con, 
    "INSERT INTO MyTable (Value) VALUES (FLOOR(RAND()*300))");

  # insert some rows
  $num = mysqli_query_id($con, 
    "INSERT INTO MyTable (Value) VALUES (100), (200), (300)");

  # clone some rows
  $num = mysqli_query_id($con, 
    "INSERT INTO MyTable (Value) ".
    "SELECT FLOOR(RAND()*Value)+1 FROM MyTable");

  mysqli_close($con);

function mysqli_query_id($con, $sql) {
  $num = -1;
  print("$sql\n");
  $res = mysqli_query($con, $sql);
  if (!$res) {
    $errno = mysqli_errno($con);
    $error = mysqli_error($con);
    print("Query failed: $errno - $error\n");
  } else {
    $id = mysqli_insert_id($con); 
    print("   ID auto-generated: $id\n");
    $num = mysqli_affected_rows($con); 
    print("   Number of affected rows: $num\n");
  }
  return $id;
}
?>

If you run the script, you will get the following output:

herong$ php mysqli-insert-id.php 

INSERT INTO MyTable (ID, Value) VALUES (9, FLOOR(RAND()*300))
   ID auto-generated: 9
   Number of affected rows: 1

INSERT INTO MyTable (Value) VALUES (FLOOR(RAND()*300))
   ID auto-generated: 10
   Number of affected rows: 1

INSERT INTO MyTable (Value) VALUES (100), (200), (300)
   ID auto-generated: 11
   Number of affected rows: 3

INSERT INTO MyTable (Value) SELECT FLOOR(RAND()*Value)+1 FROM MyTable
   ID auto-generated: 14
   Number of affected rows: 5