Show Last updated date
4 posts by 3 authors in: Forums > CMS Builder
Last Post: January 16, 2018 (RSS)
By JeffC - December 7, 2017
Hi
I have been asked to add a notification to a website that provides the last time the database was updated and what to what section. It would read something like:
[Section Name] last updated: Monday 1 January.
This forum has the function to do this, but I am not using the forum plugin, this is just for a standard cmsb installation.
Thanks
By Dave - December 22, 2017
Hi Jeff,
Just saw this old post, if you've still got that task in your queue you can do it with this custom code. Just change the table name:
// get last updated time for a table
$table = getTableNameWithPrefix("articles");
$firstResult = mysql_get_query("SELECT updatedDate FROM `$table` ORDER BY updatedDate DESC LIMIT 1");
$lastUpdated = $firstResult['updatedDate'];
$lastUpdated = date("Y-m-d H:i:s", strtotime($lastUpdated)); // date formatting: http://php.net/date
print $lastUpdated;
Hope that helps!
interactivetools.com
By JeffC - January 15, 2018
Thanks Dave
That's got me on the right track, but it's not quite what I was after. Your solution shows me how to display the last time Articles was updated. But let's say I have two tables: Articles and News. How would I display the name and date of the most recently updated table?
For example: if Articles was updated 1 January and News was updated 2nd January, my website would display:
Latest Update: News, 2 January 2018
By leo - January 16, 2018
Hi,
If your database server's operating system is not Windows, you can use this code:
$result = mysql_get_query("SELECT UPDATE_TIME, table_name FROM information_schema.tables WHERE TABLE_SCHEMA = '{database_name}' ORDER BY UPDATE_TIME DESC");
$result is an array with the last updated table's update time stamp and its name. Replace {database_name} with the database your cms is using. And you probably want to add more "WHERE" statements to filter system tables such as _accounts.
If you are using Windows, it only updates "UPDATE_TIME" on insert and delete. Which means if you update a row this "UPDATE_TIME" won't get updated. You will probably need to use this code:
$tables = ['accounts','products'];
$updatedDateArray = [];
foreach($tables as $tableName){
$resultArray = mysql_get_query('SELECT MAX(updatedDate) AS updatedDate FROM ' . getTableNameWithPrefix($tableName));
$updatedDateArray[$tableName] = $resultArray['updatedDate'];
}
$lastUpdatedDate = max($updatedDateArray);
$lastUpdatedTables = array_keys($updatedDateArray, $lastUpdatedDate);
Change $tables to be the tables you want to keep track of. $lastUpdatedDate will be the lasted date an update happened and $lastUpdatedTables will be an array with all tables that have the same lasted updatedDate.
Let me know if you have any questions!
Thanks,
interactivetools.com