5

Hi everyone I am new in web development and I am suffering from a problem to get date and time when mysql database table last updated because I have to show it on my web page. I am getting the last updated date correctly but not correct time please help me.

 <?php

    $sql = "SHOW TABLE STATUS FROM MydatabaseName LIKE 'TableName'";
    $tableStatus = mysql_query($sql);

while ($array = mysql_fetch_array($tableStatus)) {
          $updatetime = $array['Update_time'];

          $datetime = new DateTime($updatetime);
          echo $updatetime ;
     }

 ?>
Pankaj Gupta
  • 146
  • 2
  • 8
  • do you have different columns for date and time in your table ? – Priyanshu Apr 25 '16 at 05:21
  • @Priyanshu - No , I am not storing date and time in different column. I am looking for some system define method for it , which can return the last updated date and time. – Pankaj Gupta Apr 25 '16 at 05:26

3 Answers3

1

If this could help you

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

How can I tell when a MySQL table was last updated?

Community
  • 1
  • 1
Ashish
  • 647
  • 7
  • 18
  • 1
    Hi Ashish - Can you please convert the above answer in reference of php in my case I am using your code as - but getting empty array... – Pankaj Gupta Apr 25 '16 at 06:21
0

since you have tagged a question in mysql.

Did you try this? see if this is helpful.

select columns from table order by date_time column desc limit 1:
Priyanshu
  • 885
  • 6
  • 12
0

If you have a relatively recent version of MySQL, you can query information_schema.tables

 select substr(update_time,1,10) as date_updated,
        substr(update_time,12) as time_updated 
 from information_schema.tables
 where 
 table_schema = 'name_of_your_database' and 
 table_name = 'your_table_name';

Note that this may not work for user-defined tables if your MySQL engine is InnoDB. It works as advertised on a MyISAM installation.

Spade
  • 2,220
  • 1
  • 19
  • 29