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]<?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();
?>[/php]
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. |
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]<?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);
}
?>[/php]
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]<?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;
}
?>[/php]
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]<?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);
}
?>[/php]
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