-
Hajipur, Bihar, 844101
MySQL Get Last ID
In PHP and MySQL applications, you often insert new records into a table with an auto-incremented primary key. After inserting a record, you may need the ID of that newly inserted row to use in subsequent operations. This is especially common in scenarios like creating orders, invoices, or user accounts.
This tutorial explains how to retrieve the last inserted ID using PHP with MySQLi and PDO, along with best practices and examples.
What is auto-increment?
An auto-increment column automatically generates a unique number for each new row. Typically, it’s used as a primary key to uniquely identify each record.
Example table:
CREATE TABLE students (
id INT AUTO_INCREMENT PRIMARY KEY,
name VARCHAR(50) NOT NULL,
age INT NOT NULL,
email VARCHAR(100)
);
-
When you insert a new student without specifying
id, MySQL generates it automatically. -
The generated ID can be retrieved using PHP.
Why Get the Last Inserted ID?
Getting the last inserted ID is useful for:
-
Linking related records in other tables.
-
Example: After inserting a new order, insert order items referencing the order ID.
-
-
Displaying confirmation messages to users with the record ID.
-
Updating dependent tables or running transactions.
Using MySQLi to Get Last Inserted ID
MySQLi provides a built-in function mysqli_insert_id() for procedural style, and $conn->insert_id for object-oriented style.
Procedural Style Example
<?php
$conn = mysqli_connect("localhost", "root", "", "school_db");
if (!$conn) die("Connection failed: " . mysqli_connect_error());
$sql = "INSERT INTO students (name, age, email) VALUES ('Neha', 16, 'neha@example.com')";
if (mysqli_query($conn, $sql)) {
$last_id = mysqli_insert_id($conn);
echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
echo "Error: " . mysqli_error($conn);
}
mysqli_close($conn);
?>
Object-Oriented Style Example
<?php
$conn = new mysqli("localhost", "root", "", "school_db");
if ($conn->connect_error) die("Connection failed: " . $conn->connect_error);
$sql = "INSERT INTO students (name, age, email) VALUES ('Anika', 17, 'anika@example.com')";
if ($conn->query($sql) === TRUE) {
echo "New record created successfully. Last inserted ID is: " . $conn->insert_id;
} else {
echo "Error: " . $conn->error;
}
$conn->close();
?>
Using PDO to Get Last Inserted ID
PDO provides the lastInsertId() method, which works for any database supporting auto-incremented IDs.
<?php
try {
$conn = new PDO("mysql:host=localhost;dbname=school_db", "root", "");
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$sql = "INSERT INTO students (name, age, email) VALUES ('Riya', 16, 'riya@example.com')";
$conn->exec($sql);
$last_id = $conn->lastInsertId();
echo "New record created successfully. Last inserted ID is: " . $last_id;
} catch(PDOException $e) {
echo "Error: " . $e->getMessage();
}
$conn = null;
?>
Use Cases for Last Inserted ID
-
Inserting Related Records
// Insert order
$conn->exec("INSERT INTO orders (user_id, total) VALUES (1, 500)");
$order_id = $conn->lastInsertId();
// Insert order items using $order_id
$conn->exec("INSERT INTO order_items (order_id, product_id, quantity) VALUES ($order_id, 10, 2)");
-
Generating Confirmation Messages
echo "Your account has been created. Your User ID is: " . $conn->lastInsertId();
-
Transaction Handling
In a transaction, you can insert multiple rows and retrieve IDs to maintain data integrity between related tables.
Best Practices
-
Always check for errors after insertion before retrieving the last ID.
-
Retrieve the last inserted ID immediately after insertion to avoid conflicts in concurrent operations.
-
Use prepared statements for inserting user input to prevent SQL injection.
-
Be aware that
lastInsertId()is connection-specific; it will only return the last ID for the current session.
Common Errors
| Error | Cause | Solution |
|---|---|---|
| Last ID returns 0 | No auto-increment field | Ensure table has an auto-increment primary key |
| Last ID incorrect | Another insertion happened in parallel | Retrieve ID immediately after insert |
| PDOException | Connection or syntax error | Check connection and SQL syntax |
Summary of the Tutorial
-
Retrieving the last inserted ID is crucial for dynamic applications where records are interdependent.
-
Use
mysqli_insert_id()or$conn->insert_idwith MySQLi. -
Use
lastInsertId()with PDO. -
Always check for errors, handle transactions carefully, and retrieve the ID immediately after insertion.
This method is widely used in applications like e-commerce, school management systems, and user account systems to link related data reliably and efficiently.
Practice Questions
-
Write a PHP script using MySQLi procedural style to insert a new student into the
studentstable and display the last inserted ID. -
Write the same insertion using MySQLi object-oriented style and retrieve the last inserted ID.
-
Write a PHP script using PDO to insert a record into the
studentstable and print the last inserted ID. -
Explain why it is important to retrieve the last inserted ID immediately after insertion.
-
Write a PHP script that inserts an order into an
orderstable and uses the last inserted ID to insert related items into anorder_itemstable. -
Modify a PDO insertion script to handle exceptions if the insertion fails.
-
Write a PHP prepared statement using MySQLi that inserts a student record and retrieves the last inserted ID.
-
Explain the difference between
mysqli_insert_id()and PDO’slastInsertId()methods. -
Write a PHP script to insert multiple rows into a table and retrieve the last inserted ID of the final row.
-
Explain a real-world scenario where retrieving the last inserted ID is crucial, such as in an e-commerce or school management system.
