Last updated?

Need to reflect the date a table was last updated in a MySQL database

[Courtman]

I am looking for a way to show on a page the date a table in a mySQL database was last updated. Does anyone know of a simple PHP (or similar script) that can do this for me?

[lorax]

You should have a look at [dev.mysql.com...]

[Courtman]

Thanks for the link. I only need to show the last updated field for one of the tables in the database. How do I just show that one result?

[coopster]

You would need to keep a column of type DATETIME [dev.mysql.com] or TIMESTAMP [dev.mysql.com] for either that particular column or for the entire row.

Oops, hold on, I thought you meant a particular field (column) in a certain table. You mean you want just the information for a certain table in the database...?

SHOW TABLE STATUS FROM mydatabase LIKE 'mytable';

[lorax]

If you're just looking for update info on a particular field then you'll need to do as coopster suggests and include a datetime stamp of your own. If the update is for the table then you can query the MySQL server (see the link in my last post) to get that info.

[Courtman]

Its the whole table I want to check the last updated date for. Whenever I run a query on this it returns a blank field - any ideas why?

[coopster]

Don't forget you have to extract the data from the result set after fetching array values. Something like this should show you what array indexes and values are available:

$sql = "SHOW TABLE STATUS FROM mydatabase LIKE 'mytable'"; 
$rows = mysql_query [php.net]($sql); 
$row = mysql_fetch_assoc [php.net]($rows); 
print '<pre>'; 
print_r [php.net]($row); 
print '</pre>'; 

[Courtman]

Hi guys,

I'm revisiting this topic having not yet gotten around to figuring it out. When I do the script above I can see the fields and everything, but can't seem to get them into an array so I can just extract the "Last Updated" record, and then output it as dd-Mmm-yy. How can I do this or do I need to alter the SQL query to just take out the one line I need?

[timster]

If I'm following along here OK, this may do the trick:

select DATE_FORMAT(max(ts), '%d %M %y') from interests

[coopster]

Not quite, timster, as the query has already been run and the results desired are in the returned result set from the MySQL

SHOW TABLE STATUS

query. (We aren't querying a table with a date column in it).

Courtman, the result set is a single row returned as an array already, therefore there is no need to try and get them into an array, the data is already there. Run the previous short script and you'll notice the top line of output states Array. You need to do one of two things now, you could either format the date using another query statement (that doesn't retrieve any data from a table, but simply returns a formatted date), or you could use PHP to format the already returned date as Birdman alluded to earlier [webmasterworld.com]. Here is an example of the former, the latter is described in Birdman's post.

$sql = "SHOW TABLE STATUS FROM 45828_1 LIKE 'member'";  
$rows = mysql_query($sql);  
$row = mysql_fetch_assoc($rows);  
$sql = "SELECT DATE_FORMAT('" . $row['Update_time'] ."', '%d %b %Y')"; 
$row = mysql_fetch_row(mysql_query($sql)); 
print $row[0];