Get the last inserted ID of a MySQL table in PHP

Get the last inserted ID of a MySQL table is a simple query to obtain the primary key of the record you just inserted to the table. Here I have explained the query using PDO as well as Mysqli.

Also, I have abbreviated few points while using PDO or if you are trying to get the last inserted ID of a MySQL table in PHP using other methods.

Get the last inserted ID using PDO

<?php 

    $db = new PDO('mysql:host=localhost;dbname=dbtest', 'username', 'password'); 

    $stmt = $db->prepare("INSERT INTO test (name, email) VALUES(?,?)"); 
	
	$stmt->execute( array('user', 'user@example.com')); 
	
	$last_id = $db->lastInsertId(); 

?>

<?php

$db = new PDO('mysql:host=localhost;dbname=dbtest', 'username', 'password');

$stmt = $db->prepare("INSERT INTO test (name, email) VALUES(?,?)");

$stmt->execute( array('user', 'user@example.com'));

$last_id = $db->lastInsertId();

It will be better to mention some facts here to clarify few misconception regarding PDO:

You will never get the wrong ID if you use mysql_insert_id right after your mysql_query even if you have a lot of inserts happening (eg your database also logs something).
If of course you execute several other queries on the same connection in between, you may face the issue.
You don’t need to worry about other processes doing inserts. No need to wrap the query in a transaction. The mysql manual says: “The ID that was generated is maintained in the server on a per-connection basis“.
This means that the value returned by the function to a given user is the first AUTO_INCREMENT value generated for most recent statement affecting an AUTO_INCREMENT column by that user.

Get the last inserted ID using Mysqli

<?php
	$db = mysqli_connect('localhost', 'username', 'password', 'dbtest');
	
	$stmt = "INSERT INTO test (name, email) VALUES ('user', 'user@example.com')";
	
	mysqli_query($db, $stmt);

	$last_id = mysqli_insert_id($db);
?>

<?php

$db = mysqli_connect('localhost', 'username', 'password', 'dbtest');

$stmt = "INSERT INTO test (name, email) VALUES ('user', 'user@example.com')";

mysqli_query($db, $stmt);

$last_id = mysqli_insert_id($db);

In above code I have used Procedural style. You can use Object oriented style but read this article as few users have reported bug in this approach.

If you’re using Mysql then stop using mysql_* functions in new code. They are no longer maintained and are officially deprecated.

And at last if you are using SELECT MAX(id) FROM table to get the last inserted ID of a MySQL table then the main problem of this method is concurrency means you can get wrong id if another user inserted a record in between.

Get the last inserted ID using PDO

Get the last inserted ID using Mysqli

Leave a Reply Cancel reply