How to Get the Last Inserted ID of a Table in PHP?

Problem:

You just inserted a row in a table. Now, you want to know the id of that row.

Solution:

In the following, we’ll get the last inserted id by using PHP’s three APIs

  1. PHP’s MySQL Extension
  2. PHP’s MySQLi Extension
  3. PHP Data Objects(PDO)

1. PHP’s MySQL Extension

<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// Establish server connection
$link = mysql_connect($host, $user, $password);
if(!$link) {
    die("Not connected: ". mysql_error());
}else{
    // Select Database
    mysql_select_db($database, $link);
}
// run query to insert a record
mysql_query("INSERT INTO students (Name, Email, Class) values ('Joseph B. Guidry', 'JosephB@yahoo.com', '7th grade')");
echo "ID of the last inserted record is: ". mysql_insert_id();
?>

mysql table01

Output:
ID of the last inserted record is: 1

Explanation:

Line 16 The query inserts a student information in the “students” table
Line 17 mysql_insert_id() function returns the auto generated id used in the query which is 1 in the above example.

mysql_connect() is deprecated from PHP 5.5.0. So, it is recommended to use either PHP’s MySQLi Extension or PHP Data Objects (PDO). PDO is recommended over MySQLi.

2. PHP’s MySQLi Extension

You can write the mysqli code in two styles

  1. Procedural style and
  2. Object oriented style

2. a. Procedural style

<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// Establish server connection and select database
$dbh = mysqli_connect($host, $user, $password, $database);
if (mysqli_connect_errno()) {
    die('Unable to connect to database '. mysqli_connect_error());
}else{
    // run query to insert a record
    mysqli_query($dbh, "INSERT INTO students (Name, Email, Class) values ('Sandra L. Webb', 'SandraB@yahoo.com', '6th grade')");
    echo "ID of the last inserted record is: ". mysqli_insert_id($dbh);
}
?>

mysql table02

Output:
ID of the last inserted record is: 2

Explanation:

Line 13 The query inserts a student information in the “students” table
Line 14 mysqli_insert_id($dbh) returns the auto generated id used in the query which is 2 in the above example. Here, $dbh is the database connection identifier created in line 6.

2. b. Object oriented style

<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

// Establish server connection and select database
$dbh = new mysqli($host, $user, $password, $database);
if($dbh->connect_errno > 0){
    die('Unable to connect to database '. $dbh->connect_error);
}else{
    $dbh->query("INSERT INTO students (Name, Email, Class) values ('Robert D. Riney', 'Robert@yahoo.com', '5th grade')");
    echo "ID of the last inserted record is: ". $dbh->insert_id;
}
?>

mysql table03

Output:
ID of the last inserted record is: 3

Explanation:

Line 12 The query inserts a student information in the “students” table
Line 13 $dbh->insert_id returns the auto generated id used in the query which is 3 in the above example.


3. PHP Data Objects (PDO)

<?php
$host = "localhost";
$user  = "root";
$password =  "";
$database = "db_students";

try {
    // Establish server connection and select database
    $dbh = new PDO("mysql:host=$host;dbname=$database", $user, $password);
    // run query to insert a record
    $dbh->query("INSERT INTO students (Name, Email, Class) values ('Mario D. Pierce', 'Mario@yahoo.com', '7th grade')");
    echo "ID of the last inserted record is: ". $dbh->lastInsertId();
}catch(PDOException $e){
    die('Could not connect to the database:' . $e);
}
?>

mysql table04

Output:
ID of the last inserted record is: 4

Explanation:

Line 11 The query inserts a student information in the “students” table
Line 12 $dbh->lastInsertId() returns the auto generated id used in the query which is 4 in the above example. Here, $dbh is the database connection identifier created in line 9.