Get Last insert id from MySQL Table with PHP
There is the various approach of selecting the last insert id from MySQL table.
In PHP there is an inbuilt method which simply returns the last insert id according to the previous insert query.
Syntax –
last id : 4
If you are maintaining the id column manually and not using AUTO_INCREMENT in MySQL table then it is not a good choice you can go with other options.
You can learn more about mysqli_insert_id() method from here.
- Select a single row from the table in descending order and store the id.
- Select Maximum value.
- The following query gives you next AUTO_INCREMENT value from the selected table which you can use to get the last id.
SELECT AUTO_INCREMENT FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = 'user'
If your currently AUTO_INCREMENT column last value is 8 and when you execute the above query on the Table this will return 9.
In PHP there is an inbuilt method which simply returns the last insert id according to the previous insert query.
1. mysqli_insert_id()
It returns last AUTO_INCREMENT column value of the previous successfully executed insert query.Syntax –
mysqli_insert_id($connection-variable)It returns 0 when the table doesn’t have any AUTO_INCREMENT column.
2. Example
<?php
$host = "localhost"; /* Host name */
$user = "root"; /* User */
$password = ""; /* Password */
$dbname = "tutorial"; /* Database name */
$con = mysqli_connect($host, $user, $password,$dbname);
// Check connection
if (!$con) {
die("Connection failed: " . mysqli_connect_error());
}
// Insert query
$query = "insert into users(username,fname,lname) values('sonarika','Sonarika','Bhadoria')";
mysqli_query($con,$query);
// Get last insert id
$lastid = mysqli_insert_id($con);
echo "last id : ".$lastid;
?>
Outputlast id : 4
3. Conclusion
Now you know how to get last insert id value using an inbuilt method. But you need to call it immediately after the insert query because it works according to the last query.If you are maintaining the id column manually and not using AUTO_INCREMENT in MySQL table then it is not a good choice you can go with other options.
You can learn more about mysqli_insert_id() method from here.
