PHP MySQL – How To Get Last Inserted ID

It is often necessary to retrieve PHP MySQL Last Inserted ID record in a database, when inserting new records into the database using PHP.

You can use this ID as a reference in other tables or operations that are part of the application in order to make your work easier.

Throughout this article, we will be exploring a PHP solution for retrieving a MySQL database’s last inserted ID by using the PDO extension in order to retrieve the ID from that database.

We are also going to highlight some best practices that can be applied when working with ID generation and retrieval in MySQL databases so that we avoid common pitfalls.

Table Of Content-

PHP MySQL – How To Get Last Inserted ID:
- PHP MySQL Last Inserted ID:

PHP MySQL Last Inserted ID

An INSERT or UPDATE operation on a table, which has an AUTO_INCREMENT field, will allow us to get the ID of the last record that has been inserted or updated immediately once the operation has been performed.

The “id” column in the table “Users” is a field that is an AUTO_INCREMENT field.

CREATE TABLE Users (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
age INT,
gender VARCHAR(50),
reg_date TIMESTAMP DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
)

As in the examples from the previous page (PHP Insert Data Into MySQL), the following examples are identical to those from the previous page except that we have added one single line of code to retrieve the ID of the previous record that has been inserted.

Our code also echo’s the last ID that has been inserted.

Below is an example using MySQLi’s Object-Oriented method:

Example (MySQLi’s Object-Oriented):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db";// Creating connection $conn = new mysqli($servername, $username, $password, $dbname); // Checking connection if ($conn->connect_error) { die("Connection failed: " . $conn->connect_error); } $sql = "INSERT INTO Users (firstname, lastname, email, age, gender) VALUES (Matt, 'LeBlanc', '[email protected]', 55, 'Male')";//Inserting data into Users if ($conn->query($sql) === TRUE) { $last_id = $conn->insert_id; echo "New record added successfully. Last inserted ID is: " . $last_id; } else { echo "Error: " . $sql . " " . $conn->error; } $conn->close(); ?>

Below is an example using MySQLi’s Procedural method:

Example (MySQLi’s Procedural):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; // Creating connection $conn = mysqli_connect($servername, $username, $password, $dbname); // Checking connection if (!$conn) { die("Connection failed: " . mysqli_connect_error()); } $sql = "INSERT INTO Users (firstname, lastname, email, age, gender) VALUES (Matt, 'LeBlanc', '[email protected]', 55, 'Male')"; if (mysqli_query($conn, $sql)) { $last_id = mysqli_insert_id($conn); echo "New record added successfully. Last inserted ID is: " . $last_id; } else { echo "Error: " . $sql . " " . mysqli_error($conn); } mysqli_close($conn); ?>

Below is an example using MySQL PDO(PHP Data Objects) method:

Example (PDO):

<?php $servername = "localhost"; $username = "my_user"; $password = "my_pass"; $dbname = "mrx_test_db"; try { $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password); // Specify the exception error mode in the PDO $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql = "INSERT INTO Users (firstname, lastname, email, age, gender) VALUES (Matt, 'LeBlanc', '[email protected]', 55, 'Male')"; // Due to the fact that no results are returned, use exec() instead $conn->exec($sql); $last_id = $conn->lastInsertId(); echo "New record added successfully. Last inserted ID is: " . $last_id; } catch (PDOException $e) { echo $sql . " " . $e->getMessage(); } $conn = null; ?>

Example Explanation

Above example demonstrates how to retrieve the last inserted ID in a MySQL database using PDO after inserting a new record.

Here’s a step-by-step explanation of what we did:

The code starts by defining the variables $servername, $username, $password, and $dbname, which are used to specify database connection details.
A try-catch block is used to handle any exceptions that may occur during the database connection and query execution. Inside the try block, the PDO constructor is used to create a new database connection. The connection string is created by combining the servername, dbname, username, and password variables.
The PDO::setAttribute() method is called on the $conn object to set the error mode to ERRMODE_EXCEPTION, which means that PDO will throw an exception if an error occurs.
A SQL INSERT statement is defined in the $sql variable. The statement inserts a new record into the “Users” table with the values ‘Matt‘ for firstname, ‘LeBlanc‘ for lastname, ‘[email protected]‘ for email, 55 for age, and ‘Male‘ for gender.
The $conn->exec() method is called to execute the INSERT statement. Because the statement does not return any results, the exec() method is used instead of query().
The $conn->lastInsertId() method is called to retrieve the ID of the last inserted record.
The ID is stored in the $last_id variable.
If the INSERT statement is executed successfully, the message “New record added successfully. Last inserted ID is: ” is displayed along with the value of $last_id.
If an exception is caught, the catch block is executed. The catch block displays an error message that includes the SQL query and the error message returned by PDO.
Finally, the $conn variable is set to null to close the database connection.

We value your feedback.