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

Solution of how to get the last inserted ID of a table.

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.


Pin It
  • http://www.prabaweb.webs.com/ Prabakar

    well

    • neilfin

      Prabakar, happy to help you.

  • Muchamad D Fadilah

    I point it, thanks.

    • neilfin

      You welcome. Let me know if you need more help.

  • sudhakar pathak

    i just want to display last record in HTML text box please help me aout
    <?php
    //Establishing Connection with Server
    $connection = mysql_connect("localhost", "root", " ");
    //Selecting Database from Server
    $db = mysql_select_db("mysql", $connection);
    if(isset($_POST['submit'])){
    //Fetching variables of the form which travels in URL
    $ammount= $_POST['ammount'];
    $firstname= $_POST['txtfirstname'];
    $lastname= $_POST['txtlastname'];
    $address1= $_POST['txtaddress1'];
    if($firstname !=''||$email !=''){
    //Insert Query of SQL
    $query = mysql_query("insert into personal_info(MAmount,firstname, lastname, address1,) values ('$ammount','$firstname','$lastname','$address1')");
    echo "Data Inserted successfully…!!“;}
    else{
    echo “Insertion Failed Some Fields are Blank….!!”; }
    } //Closing Connection with Server
    mysql_close($connection);
    ?>

    Thanks in advance

  • Bong Khàï

    I use PDO connection with prepare statement by $conn->execute(); to execute the command sql. I used this I cannot get $conn->lastInsertID. It’s always return 0. please solve me… thank you in advance

↑ Back to Top