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
- Find table update using date column (using max)
- Find table update date/time (using information_schema)
- Find table creation date/time (using information_schema)
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];
admin