Get last inserted record ID from MySQL using PHP

When a table have AUTO_INCREMENT field as its primary key. In that case you mostly require to get that field’s ID which is auto generated by MySQL. So you can further use that ID for getting further information or connect different table records with each other.

You can get ID while performing INSERT and UPDATE query on MySQL.

There are three types of Queries you can perform to get Last Insert ID from MySQL.

• Get Last Insert ID by MySQLi Object-oriented
• Get Last Insert ID by MySQLi Procedural
• Get Last Insert ID by PDO

PHP Get ID of Last Inserted Record

Before we start to know how to get last inserted ID record. We need to make sure the table exists. And if table does not exist we will create the table.

Establish Database Connection PHP MySQL

Before you can execute any query you need to connect with database. Recent MySQLI helps you to hold database connection as an object. Let’s make a database connection first. To establish database connection you need, database name, database username and database user password. Make sure Database Username is connected with database with all required rights.

<?php
//Database Connection file. Update with your Database information once you create database from cpanel, or mysql.
	define ("DB_HOST", "localhost"); //Databse Host.
	define ("DB_USER", "root"); //Databse User.
	define ("DB_PASS", ""); //database password.
	define ("DB_NAME", "posnew"); //database Name.

$db = new mysqli(DB_HOST, DB_USER, DB_PASS, DB_NAME);
if($db->connect_errno > 0){
    die('Unable to connect to database ['.$db->connect_error.']');
}

By using code above you can easily establish a database connection. Now the $db-> object holds the connection and can run SQL queries.

How to check PHP MySQL if table exists

Let’s assume we have to check if table with name users_meta exists or not. To verify if MySQL table exists with PHP we need to run SQL query. This query SELECT 1 from users_meta table will return false if table does not exist else it will return true. In example below $db-> object holds connection of database. Let’s now check if table exists or not.

if($db->query('SELECT 1 from user_meta') == FALSE) { 
    //Table does not exists
} else {
  //Table exists run your query. 
}

This is not necessary you check table’s existence all time on every query. Its just to verify when you don’t know or don’t have access to PHPmyadmin to verify.

Create MySQL database table with PHP

Before you create MySQL table in database using PHP SQL query. The great thing is you plan your table’s fields. You would also require to define your primary key as Auto increment data type. Other fields like description varchar, number int or float so on. Let’s run the query as we already have established the database connection to create table.

if($db->query('SELECT 1 from user_meta') == FALSE) { 
	$query = 'CREATE TABLE user_meta (
		`user_meta_id` bigint(20) NOT NULL AUTO_INCREMENT,
		`user_id` bigint(20) NULL,
		`message_email` varchar(50) NULL,
		`last_login_time` datetime NULL,
		`last_login_ip` varchar(120) NULL,
		`login_attempt` bigint(20) NULL,
		`login_lock` varchar(50) NULL,
		PRIMARY KEY (`user_meta_id`)
	)';	
	$result = $db->query($query) or die($db->error);
	echo 'User Meta Table created.<br>';
}  //Creating user notes table ends here.

In code above user_meta_id as bigint() is a Not Null Auto increment field. Which later defined and Primary Key. And this is the field which we can get from INSERT and UPDATE queries Last Insert ID.

Get Last Insert ID by MySQLi Object-oriented

We recommend you to use Object-oriented approach for establishing database connections and running SQL queries with PHP. Cause PHP is steadily moving towards completely object-oriented approach. Procedural approach will be not supported in future versions of PHP.

Let’s now have a look to example which returns the last insert or update ID.

Insert Query

Following Example demonstrate how to get Last Insert ID from INSERT query. Later we will view update query.

$query = "INSERT INTO user_meta(user_meta_id, user_id,message_email,last_login_time) VALUES (NULL, '$user_id', '$message_email', '$last_login_time')";
$result = $db->query($query) or die($mysqli->error);
//Now the variable $user_meta_id holds the last insert or updated ID
$user_meta_id = $db->insert_id;

UPDATE Query

If you are updating a row using row ID in that case you would not need to get last insert id. But if you are updating a row by some other identifier let’s say email then you would need to get last updated row id. Let’s check the example below.

$query = 'UPDATE users SET
		first_name = "'.$first_name.'",
		profile_image = "'.$profile_image.'",
		description = "'.$description.'"
WHERE user_id="'.$user_id.'"';
}
$result = $db->query($query) or die($mysqli->error);
//Now the variable $user_meta_id holds the last insert or updated ID
$user_meta_id = $db->insert_id;

Get Last Insert ID by MySQLi Procedural

<?php
	// Create connection
	$db = mysqli_connect(HOST, USER, PASSWORD, DBNAME);

	// Check connection
	if (!$db) {
		die("Connection failed: " . mysqli_connect_error());
	}

	$query = "INSERT INTO users_meta (firstname, lastname, email)
	VALUES ('John', 'Doe', '[email protected]')";

	if (mysqli_query($db, $query)) {
		$last_row_id = mysqli_insert_id($db);
		echo "New record created successfully. Last inserted ID is: " . $last_row_id;
	} else {
		echo "Error: " . $sql . "<br>" . mysqli_error($conn);
	}

I hope i helped you. If you have any questions you can post below.