Hi friends, in this tutorial we will learn how to get last record in MySQL using PHP. This is very useful and almost required in any kind of web application while creating a dynamic website or any customized software.
Actually, there are two ways by which we can get the last inserted record from the MySQL database with the help of a MySQL query inside a PHP script and display the desired results.
Also read, PHP code to retrieve data from MySQL database and display
The ways to get the last record in mysql using PHP
- order by
- timestamp i.e. created at
The order by method to fetch the data from MySQL database
By using the order by method, you can first sort the data in descending order according to the primary key ‘id‘ and then putting the LIMIT 1 i.e. we need only the last inserted record. If you do not put LIMIT 1 then it will fetch all records in descending order.
Query:-
SELECT * FROM employee_info ORDER BY id DESC LIMIT 1
Example:- In order to explain the example, I am using a table employee_info from my database as shown below
PHP Code:-
<?php
$servername='localhost';
$username="root";
$password="";
try
{
$con=new PDO("mysql:host=$servername;dbname=test_db",$username,$password);
$con->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
//echo 'connected';
}
catch(PDOException $e)
{
echo '<br>'.$e->getMessage();
}
//get the last record from the database
$stmt = $con->query("SELECT * FROM employee_info ORDER BY id DESC LIMIT 1");
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
<html>
<head>
<title>ajax example</title>
<link rel="stylesheet" href="bootstrap.css" crossorigin="anonymous">
<!-- Optional theme -->
<link rel="stylesheet" href="bootstrap-theme.css" crossorigin="anonymous">
<style>
.container{
width:50%;
height:30%;
padding:20px;
}
</style>
</head>
<body>
<div class="container">
<table class="table">
<thead>
<tr>
<th>ID</th>
<th>Name</th>
<th>Phone</th>
<th>Age</th>
<th>Department</th>
</tr>
</thead>
<tbody>
<?php
if(!$rows){
echo '<tr>Data Not Found</tr>';
}
else{
foreach($rows as $row){
echo '<tr>
<td>'.$row['id'].'</td>
<td>'.$row['name'].'</td>
<td>'.$row['phone_no'].'</td>
<td>'.$row['age'].'</td>
<td>'.$row['department'].'</td>
</tr>';
}
}
?>
</tbody>
</table>
</div>
<script src="jquery-3.2.1.min.js"></script>
<script src="bootstrap.min.js"></script>
</body>
</html>
Result:-
Timestamp to get the last inserted record in PHP
In this case, you need to sort the data in descending order first according to the primary key ‘id‘, and then LIMIT 1 should be added in the query. LIMIT 1 will be applicable in both cases.
Query:-
SELECT * FROM employee_info ORDER BY created_at DESC LIMIT 1
The example is the same as the above example except for the select query.
PHP Code:-
<?php
$servername='localhost';
$username="root";
$password="";
try
{
$con=new PDO("mysql:host=$servername;dbname=test_db",$username,$password);
$con->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
//echo 'connected';
}
catch(PDOException $e)
{
echo '<br>'.$e->getMessage();
}
//get the last record from the database using timestamp
$stmt = $con->query("SELECT * FROM employee_info ORDER BY created_at DESC LIMIT 1");
$stmt->execute();
$rows = $stmt->fetchAll(PDO::FETCH_ASSOC);
?>
CONCLUSION:- I hope this article will help you to understand the concept. If you have any doubt then please leave your comment below.
What’s Happening i am new to this, I stumbled upon this I have discovered
It positively useful and it has aided me out loads.
I am hoping to give a contribution & assist different customers like its helped me.
Good job.