How to find when table was last updated using PHP MySQL

Last updated on April 8th, 2022 at 11:34 am

It is always a requirement for the web developers to display the date in which their website record / table was last updated or the last date when a user has posted some comments etc., Here I am going to explain how to

Note: Unless your table has a date column we have to use information_schema to get the last updated date / time. So the user connecting to fetch data should have access to the information_schema database.

Find table update with date column (Without information_schema)

This query can be used if your MySQL table has a date type column. We are using max() function to get the last updated date.

$qry = "SELECT MAX(<COLUMN_NAME_OF_TABLE>) FROM <TABLE_NAME>";

COLUMN_NAME_OF_TABLE will be a date type column.

TABLE_NAME is the name of the table

PHP Code

$conn = mysqli_connect($servername, $username, $password, $dbname);
$qry = "SELECT MAX(<COLUMN_NAME_OF_TABLE>) FROM <TABLE_NAME>";
$result = mysqli_query($conn,$qry);
$date = mysqli_fetch_array($result);
echo "The Record Was Last Updated On ".$date[0];

Find table update date/time (Using Information_schema)

information_schema stores information about the MySQL server such as the name of a database or table, the data type of a column. This has a table named tables. The column we are interested in that is the UPDATE_TIME.

SQL Query

SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = '' AND TABLE_NAME = '';

Replace the above query values for TABLE_SCHEMA that will be the database name where the table resides and TABLE_NAME that is the name of the table according to your requirement.

PHP Code

$servername = "localhost";
$username = "";
$password = ";
$dbname = "information_schema";
$db="";
$tablename="";
$conn = mysqli_connect($servername, $username, $password, $dbname);
$qry = "SELECT UPDATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA ='$db' AND TABLE_NAME = '$tablename';";
$result = mysqli_query($conn,$qry);
$date = mysqli_fetch_array($result);
echo "The Record Was Last Updated On ".$date[0];

Find table creation date/time (Using Information_schema)

In the information_schema we have a column named CREATE_TIME. For getting the creation time of the table use this column.

SQL Query

SELECT CREATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA = '' AND TABLE_NAME = '';

PHP Code

$servername = "localhost";
$username = "";
$password = "";
$dbname = "information_schema";
$db="";
$tablename="";
$conn = mysqli_connect($servername, $username, $password, $dbname);
$qry = "SELECT CREATE_TIME FROM information_schema.tables WHERE TABLE_SCHEMA ='$db' AND TABLE_NAME = '$tablename';";
$result = mysqli_query($conn,$qry);
$date = mysqli_fetch_array($result);
echo "The Record Was Created On  ".$date[0];
Click to rate this tutorial!
[Total: 2 Average: 4.5]

Leave a Reply

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