mysql last inserted id – 3 ways to get last inserted id of a MySQL table

mysql last inserted id: This best post will learn how to get the last inserted id of a MySQL database table using 4 different methods Question: How to Get the ID of Last Inserted Row?.

mysql last inserted id

Let us get started by step by step the sample data. I will be creating a simple database table, member_specification, followed by inserting rows into it.

No Function Support Used functions
1 MySQLi Object-oriented $last_id = $conn->insert_id;
2 MySQLi Procedural $last_id = mysqli_insert_id($conn);
3 PDO – Example $last_id = $conn->lastInsertId();
4 Mysql – Example $last_id =mysql_insert_id();

#create the table member_specification
CREATE TABLE member_specification (
      id int NOT NULL AUTO_INCREMENT,
      member_id int DEFAULT NULL,
      member_name varchar(255) DEFAULT NULL,
      member_specification varchar(255) DEFAULT NULL,
      PRIMARY KEY (id)
    );
# insert rows to member_specification
    INSERT INTO member_specification (member_id,member_name,member_specification) 
    VALUES(10,"Bhavika","Office Girl");
    INSERT INTO member_specification (member_id,member_name,member_specification) 
    VALUES(20,"Dimple","Office Assistant");
    INSERT INTO member_specification (member_id,member_name,member_specification) 
    VALUES (30,"Dhara","Office Assistant");
    INSERT INTO member_specification (member_id,member_name,member_specification) 
    VALUES(40,"Sejal","Senior Office Girl");
    INSERT INTO member_specification (member_id,member_name,member_specification) 
    VALUES(50,"Jalpa","Office Assistant");
    INSERT INTO member_specification (member_id,member_name,member_specification) 
    VALUES(60,"Purvisha","Office Girl");
    INSERT INTO member_specification (member_id,member_name,member_specification) 
        VALUES(70,"Ankita","Office Girl"); 

How to get last inserted id of a MySQL table using max() function

 SELECT MAX( id ) FROM member_specification;

How to get last inserted id of a MySQL table using ORDER BY DESC

SELECT id FROM member_specification ORDER BY id DESC LIMIT 1;

How to get last inserted id of a MySQL table using LAST_INSERT_ID()

SELECT LAST_INSERT_ID();

How to get last inserted id of a MySQL table using AUTO_INCREMENT
Not: If you are using AUTO_INCREMENT with column, then you can use php best function like last_insert_id() method.

SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'member_specification';

Ways to Retrieve ID: Code Examples

index.php

<?php
  $host = 'localhost';
  $user = 'root';
  $pass = '[email protected]';
  $db = 'blog_v1';

  $conn = new mysqli($host, $user, $pass, $db);  	
  	
  if ($conn->connect_error) {      
    die("Failed to connect: " . $conn->connect_error);
  }
  $sql = "INSERT INTO members (membername, password, member_email) VALUES ('laravel', 'information', '[email protected]')";  	
  if ($conn->query($sql) === TRUE) { 
    $latest_id = $conn->insert_id;    
    echo "Good Luck Insert successful. Latest ID is: " . $latest_id;
  } else { 
    echo "Error: " . $sql . "<br>" . $conn->error;
  }
  $conn->close();  
?>

Access last inserted row in MySQL?

mysql> select *from YourTblName where Id=(SELECT LAST_INSERT_ID());

query to get the last inserted row : mysql last inserted id

select last_insert_id();

Example : PHP MySQL Last Inserted ID

<?php

$conn = mysqli_connect("localhost", "root", "", "exmaple");
 
if($conn === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 

$sql = "INSERT INTO members (first_name, last_name, email) VALUES ('bhavika', 'Dave', '[email protected]')";
if(mysqli_query($conn, $sql)){
    $last_id = mysqli_insert_id($conn);
    echo "Good Luck, Your Records inserted successfully. Last inserted ID is: " . $last_id;
} else{
    echo "Sorry, ERROR: Could not able to execute $sql. " . mysqli_error($conn);
}
 
mysqli_close($conn);
?>

I hope you get an idea about mysql last inserted id.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.