PHP & MySQL - Get Last ID
The PHP mysqli_insert_id() function can be used to get the ID generated by an INSERT or UPDATE statements on a table with a column having the AUTO_INCREMENT attribute. In the case of a multiple-row INSERT statement, it returns the first automatically generated value that was successfully inserted.
Get Last ID - Object-oriented style
Consider a table called Employee which contains four columns: EmpID, Name, City, and Salary. The EmpID column of this table is an AUTO_INCREMENT field.
CREATE TABLE Employee ( EmpID INT AUTO_INCREMENT PRIMARY KEY, Name VARCHAR(255) NOT NULL, City VARCHAR(100), Salary DECIMAL(18,2) );
Assuming that the table contain no records at present. The example below demonstrates how to get the last id after performing insert operation using object-oriented style.
<?php //establishing connection to the database $mysqli = new mysqli("localhost", "user", "password", "database"); if ($mysqli->connect_errno) { echo "Failed to connect to MySQL: ". $mysqli->connect_error; exit(); } //inserting first record $query = "INSERT INTO Employee (Name, City, Salary) VALUES ('John', 'London', 2800)"; $mysqli->query($query); //getting the last insert id printf("New record has ID %d.\n", $mysqli->insert_id); //inserting two records $query = "INSERT INTO Employee (Name, City, Salary) VALUES ('Marry', 'Paris', 2750)"; $mysqli->query($query); $query = "INSERT INTO Employee (Name, City, Salary) VALUES ('Jo', 'Amsterdam', 2900)"; $mysqli->query($query); //getting the last insert id printf("New record has ID %d.\n", $mysqli->insert_id); //closing the connection $mysqli->close(); ?>
The output of the above code will be:
New record has ID 1. New record has ID 3.
Get Last ID - Procedural style
The same can be achieved using procedural style with following script:
<?php //establishing connection to the database $mysqli = mysqli_connect("localhost", "user", "password", "database"); if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: ". mysqli_connect_error(); exit(); } //inserting first record $query = "INSERT INTO Employee (Name, City, Salary) VALUES ('John', 'London', 2800)"; mysqli_query($mysqli, $query); //getting the last insert id printf("New record has ID %d.\n", mysqli_insert_id($mysqli)); //inserting two records $query = "INSERT INTO Employee (Name, City, Salary) VALUES ('Marry', 'Paris', 2750)"; mysqli_query($mysqli, $query); $query = "INSERT INTO Employee (Name, City, Salary) VALUES ('Jo', 'Amsterdam', 2900)"; mysqli_query($mysqli, $query); //getting the last insert id printf("New record has ID %d.\n", mysqli_insert_id($mysqli)); //closing the connection mysqli_close($mysqli); ?>
The output of the above code will be:
New record has ID 1. New record has ID 3.
Complete PHP MySQLi Reference
For a complete reference of all properties, methods and functions of PHP MySQLi extension, see PHP MySQLi Reference.