Jump to content

"getlastmod" equivalent for MySQL


DevinC

Recommended Posts

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.