How to Acquire MySQL Last Insert ID and Keep Track of Your Data Easily

We have covered the basics on how to store data in your databases. However, when you get into it, it might get hard to keep track of your data entries. Getting MySQL last insert ID is a useful function that lets you quickly and easily retrieve the ID of the last record you added to your database.

You can make MySQL get last insert ID by merely using a few simple statements, such as mysql_insert_id(). In this tutorial, we will provide code examples, illustrating the ID retrieval process using MySQLi and POD.

Additionally, we will explain other functions to apply when you want to get the ID of the latest record. For instance, echo statement is used to output the result.

MySQL Last Insert ID: Main Tips

• PHP allows you to get the ID of the record inserted last.
• This process helps you track the number of records you currently have in a table.
• One of the most common ways to get MySQL last insert ID is using the mysql_insert_id() statement.

Ways to Retrieve ID: Code Examples

You might remember from our previous lessons that when a table includes an AUTO_INCREMENT column, MySQL generates the ID automatically. Remember that when you are using insert or update functions.

Let's look at a piece of script meant to create a table called users. You can see the column user_id is set to AUTO_INCREMENT:

CREATE TABLE users ( user_id INT(11) UNSIGNED AUTO_INCREMENT PRIMARY KEY, username VARCHAR(40) NOT NULL, password VARCHAR(40) NOT NULL, email VARCHAR(60), register_date TIMESTAMP )

In the code examples provided below, you will see how we add a line of code to retrieve MySQL last insert ID. We use echo statement to display it.

Just like in our previous lessons on how to insert and delete table data, the three examples represent three database connection types. You will see how to perform MySQLi object oriented, MySQLi procedural and PDO last insert ID search.

Let's start with getting the MySQL last inserted ID using MySQLi object-oriented connection:

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';
  // Create connection
  $conn = new mysqli($host, $user, $pass, $db);  	
  // Check connection  	
  if ($conn->connect_error) {      
    die("Failed to connect: " . $conn->connect_error);
  }
  $sql = "INSERT INTO users (username, password, email) VALUES ('Johny', 'Dawkins', 'johny@example.com')";  	
  if ($conn->query($sql) === TRUE) { 
    $latest_id = $conn->insert_id;    
    echo "Insert successful. Latest ID is: " . $latest_id;
  } else { 
    echo "Error: " . $sql . "<br>" . $conn->error;
  }
  $conn->close();  
?>

If you are using MySQLi procedural connection, you can make MySQL get last insert ID by simply using mysql_insert_id() statement:

<?php
  $host = 'host';
  $user = 'user';
  $pass = 'pass';
  $db = 'db';
  // Create connection  
  $conn = mysqli_connect($host, $user, $host, $db);  	
  // Check connection  	
  if (!$conn) { 
    die("Failed to connect: " . mysqli_connect_error());
  }
  $sql = "INSERT INTO users (username, password, email) VALUES ('Johny', 'Dawkins', 'johny@example.com')";  	
  if (mysqli_query($conn, $sql)) { 
    $latest_id =  mysqli_insert_id($conn);    
    echo "Insert successful. Latest ID is: " . $latest_id;
  } else { 
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
  }
  mysqli_close($conn);  
?>

In PDO last insert ID can be retrieved this way:

<?php
  $host = 'host';
  $user = 'user';  
  $pass = 'pass';
  $db = 'db';
  try {
    $conn = new PDO("mysql:host=$host;dbname=$db", $user, $pass);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO users (username, password, email) VALUES ('Johny', 'Dawkins', 'johny@example.com')";	
    // use exec() because no results are returned
    $conn->exec($sql);
    $latest_id = $conn->lastInsertId();
    echo "Insert successful. Latest ID is: " . $latest_id;
  } catch(PDOException $e) { 
    echo $sql . "<br>" . $e->getMessage(); 
  }
  $conn = null; 
?>

MySQL Last Insert ID: Summary

• Using MySQL and PHP, it is easy to fetch the ID of the last entry you inserted.
• Thanks to this function, you can always know how many records you have inserted.
• If you are using MySQLi procedural connection, the easiest way to get the ID of the last entry is calling mysql_insert_id() statement.