"getlastmod" equivalent for MySQL

[DevinC]

Hello, is there a function available that will do the same thing as PHP's GETLASTMOD but can be use to find out the last modification date/time of a MySQL database? PHP's GETLASTMOD is limit to the current page only.

Thanks,

Devin

[Barand]

MySql has a SHOW TABLE STATUS command the output from which includes when last updated

[code]<?php
$res = mysql_query("SHOW TABLE STATUS FROM dbName LIKE 'tableName'") ;

echo date('jS M Y H:i:s', strtotime(mysql_result($res,0,'Update_time')));
?>[/code]

[DevinC]

Hi Barand,

Thank you for your quick response. I've checked the function SHOW TABLE STATUS in MySQL's reference
and indeed it is in there. Unfortunately, I am not all too familiar with MySQL programming. Is there a
similar or quicker way that I can obtain the info by using PHP instead?

Basically, I want PHP to output the last modification date made to the database itself when viewing
a HTML page (served by PHP) that contain data extracted from a MySQL database.

Thanks,

Devin

[kalivos]

That's exactly what Barand answered...

create file called table_status.php, put this in it:
[code]<?php


//set these vars
$mysql_user = "your mysql username";
$mysql_password = "your mysql password";
$database_name = "dbName";
$tableName = "tableName";


$link = mysql_connect('localhost', $mysql_user, $mysql_password);
if (!$link) {
  die('Could not connect: ' . mysql_error());
}

$res = mysql_query("SHOW TABLE STATUS FROM $database_name LIKE '$tableName'") ;

echo date('jS M Y H:i:s', strtotime(mysql_result($res,0,'Update_time')));
mysql_close($link);
?>[/code]

Just set the vars.

[DevinC]

Hi Kalivos and others,

Thank you for clarifying the issue. The code works for me however after playing with it for a while, I have discovered that I get different timestamp result depending on the table being monitored.

This is more complicate than I have initially thought because of several tables used by the web application and all of them produce different result. I have already tried all tables and none of them has generated satisfactory result.

Basically, I'd like to implement a system to monitor my working database on the webhost's shared webserver. Sometimes, my webhost provider have restored my database to an older backup copy without letting me know. Unaware of the issue, I have modified an older database rather than working with the latest one. As a result, I have inadvertently created two different version of the same database.

In order to implement a system to monitor my working database, here's an idea of what I need to do:
1) develop a cron script to obtain the last modification date/time of the database and log the output to a file on the server
2) develop a Scheduled Tasks/cron script to run on my local machine. The script will routinely query the remote database on the server at regular interval (ie. once every 15 minute) and it will automatically obtain the latest timestamp value.
3) the script running on my local machine will then compare the latest data (obtained from the server) with the previous last known good update.
4) if last mod date is newer than the previous last known good update, update the value of last known good update, and reset script for next check (i.e. next 15 minute)
5) if last mod date is older than the previous last known good update, generate an email alert to the admin, set the database on server to read-only mode to prevent further modification, and stop the cron script until it has been restarted by the site admin.

In order to do step 2 and 5, I need a way to connect to the server to communicate with the database without phpMyAdmin since my goal is to automate the checking.

Perhap there is a more effective solution available for the website admin to monitor their working database? Surely, I can't be the only person experiencing this problem!

Thank you in advance for your feedback.

Regards,

Devin

[Barand]

I haven't used it myself, but is Mysql's replication faclility an answer to your needs?

[DevinC]

Hi Barand,

Sorry I should have made this clear earlier. I am an enduser who use a shared webhost server. I do not have administrative privilege to control the server environment.

Nevertheless, I have forward this information to my webhost provider's server admin team to review the material and hopefully, they'll implement it if they haven't already.

There's no guarantee that they will do it. So at the meantime, I still need a solution to monitor my working database and have it notify me by email if the database has been restored to an older backup by my webhost provider.

Thanks,

Devin