How to get last record in MySQL using PHP


How to get last record in MySQL using PHP

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

How to get last record in MySQL using PHP

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:-

How to get last record in MySQL using PHP

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.


Leave a Reply

Your email address will not be published. Required fields are marked *

Theme of 96 THEME.