There is the various approach of selecting the last insert id from MySQL table.
- 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 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.
It returns last AUTO_INCREMENT column value of the previous successfully executed insert query.
It returns 0 when the table doesn’t have any AUTO_INCREMENT column.
<?php // Connection $con = mysql_connect('localhost', 'root', '') or die("Unable to connect"); // selecting database $db = mysql_select_db('tutorial', $con) or die("Database not found"); // Insert query $query = "insert into users(username,fname,lname) values('sonarika','Sonarika','Bhadoria')"; mysql_query($query); // Get last insert id $lastid = mysql_insert_id(); echo "last id : ".$lastid; ?>
last id : 4
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 mysql_insert_id() method from here.