Problem:
You just inserted a row in a table. Now, you want to know the id of that row.
Solution:
In the following, we’ll get the last inserted id by using PHP’s three APIs
- PHP’s MySQL Extension
- PHP’s MySQLi Extension
- PHP Data Objects(PDO)
1. PHP’s MySQL Extension
<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "db_students";
// Establish server connection
$link = mysql_connect($host, $user, $password);
if(!$link) {
die("Not connected: ". mysql_error());
}else{
// Select Database
mysql_select_db($database, $link);
}
// run query to insert a record
mysql_query("INSERT INTO students (Name, Email, Class) values ('Joseph B. Guidry', 'JosephB@yahoo.com', '7th grade')");
echo "ID of the last inserted record is: ". mysql_insert_id();
?>
Output:
ID of the last inserted record is: 1
Explanation:
| Line 16 | The query inserts a student information in the “students” table |
| Line 17 | mysql_insert_id() function returns the auto generated id used in the query which is 1 in the above example. |
mysql_connect() is deprecated from PHP 5.5.0. So, it is recommended to use either PHP’s MySQLi Extension or PHP Data Objects (PDO). PDO is recommended over MySQLi.
2. PHP’s MySQLi Extension
You can write the mysqli code in two styles
- Procedural style and
- Object oriented style
2. a. Procedural style
<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "db_students";
// Establish server connection and select database
$dbh = mysqli_connect($host, $user, $password, $database);
if (mysqli_connect_errno()) {
die('Unable to connect to database '. mysqli_connect_error());
}else{
// run query to insert a record
mysqli_query($dbh, "INSERT INTO students (Name, Email, Class) values ('Sandra L. Webb', 'SandraB@yahoo.com', '6th grade')");
echo "ID of the last inserted record is: ". mysqli_insert_id($dbh);
}
?>
Output:
ID of the last inserted record is: 2
Explanation:
| Line 13 | The query inserts a student information in the “students” table |
| Line 14 | mysqli_insert_id($dbh) returns the auto generated id used in the query which is 2 in the above example. Here, $dbh is the database connection identifier created in line 6. |
2. b. Object oriented style
<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "db_students";
// Establish server connection and select database
$dbh = new mysqli($host, $user, $password, $database);
if($dbh->connect_errno > 0){
die('Unable to connect to database '. $dbh->connect_error);
}else{
$dbh->query("INSERT INTO students (Name, Email, Class) values ('Robert D. Riney', 'Robert@yahoo.com', '5th grade')");
echo "ID of the last inserted record is: ". $dbh->insert_id;
}
?>
Output:
ID of the last inserted record is: 3
Explanation:
| Line 12 | The query inserts a student information in the “students” table |
| Line 13 | $dbh->insert_id returns the auto generated id used in the query which is 3 in the above example. |
3. PHP Data Objects (PDO)
<?php
$host = "localhost";
$user = "root";
$password = "";
$database = "db_students";
try {
// Establish server connection and select database
$dbh = new PDO("mysql:host=$host;dbname=$database", $user, $password);
// run query to insert a record
$dbh->query("INSERT INTO students (Name, Email, Class) values ('Mario D. Pierce', 'Mario@yahoo.com', '7th grade')");
echo "ID of the last inserted record is: ". $dbh->lastInsertId();
}catch(PDOException $e){
die('Could not connect to the database:' . $e);
}
?>
Output:
ID of the last inserted record is: 4
Explanation:
| Line 11 | The query inserts a student information in the “students” table |
| Line 12 | $dbh->lastInsertId() returns the auto generated id used in the query which is 4 in the above example. Here, $dbh is the database connection identifier created in line 9. |
well
Prabakar, happy to help you.
I point it, thanks.
You welcome. Let me know if you need more help.
i just want to display last record in HTML text box please help me aout
<?php
//Establishing Connection with Server
$connection = mysql_connect("localhost", "root", " ");
//Selecting Database from Server
$db = mysql_select_db("mysql", $connection);
if(isset($_POST['submit'])){
//Fetching variables of the form which travels in URL
$ammount= $_POST['ammount'];
$firstname= $_POST['txtfirstname'];
$lastname= $_POST['txtlastname'];
$address1= $_POST['txtaddress1'];
if($firstname !=''||$email !=''){
//Insert Query of SQL
$query = mysql_query("insert into personal_info(MAmount,firstname, lastname, address1,) values ('$ammount','$firstname','$lastname','$address1')");
echo "Data Inserted successfully…!!“;}
else{
echo “Insertion Failed Some Fields are Blank….!!”; }
} //Closing Connection with Server
mysql_close($connection);
?>
Thanks in advance
I use PDO connection with prepare statement by $conn->execute(); to execute the command sql. I used this I cannot get $conn->lastInsertID. It’s always return 0. please solve me… thank you in advance