Old Records

[vtaylor263]

Dear PHP Developers:

I need assistance with a minor script.

THe script below:

mysql_connect($hostname,$username,$password);

$query = "DELETE FROM Validation WHERE AccumulatedTime >= AllottedTime" or die('Error: Unable to delete expired passwords.');
$query2 = "DELETE FROM Validation WHERE StartDateTime -10 < CURRENT_TIMESTAMP()" or die('Error: Unable to delete old records.');

// Check database and make deletions if necessary

mysql_db_query($dbName, $query);
mysql_db_query($dbName, $query2);

print mysql_error();

mysql_close;

---

Issue: (Problem with Query 2 erasing the correct records)

The script should delete records that are 10 older than the
Current_TimeStamp. The -10 tends to erase all records that are between 1 and 10 days old. I only want to delete records that are 10 days old. This will be a Cron job.

By the way, the StartDateTime field is a DateTime formatted field.

And, the script runs fine without errors.

VT.

Any suggestions.

[kevinsequeira]

try this

DELETE FROM Validation WHERE StartDateTime +10 = CURRENT_TIMESTAMP()

for 10 days old records

DELETE FROM Validation WHERE StartDateTime +10 > CURRENT_TIMESTAMP()

for records greated than 10 days

reg
kevin

[vtaylor263]

This had an adverse effect on the records erase. It erased all of the records.

+10=

or

• 10 >

erased all records

Are there other options?

[kevinsequeira]

take a look

http://www.mysql.com/doc/en/Date_and_time_functions.html

reg
kevin

[vtaylor263]

I went back to my original script. It looks like it is closer to resolving the issue.

In my database, I have dates like 06/18/2003 10:00:00 and
06/15/2003 10:00:00, and 06/24/2003 10:00:00.

with my current script I am trying to erase records that are two or more days old.

when I run the query:

$query2 = "DELETE FROM Validation WHERE StartDateTime -2 < CURRENT_TIMESTAMP()" or die('Error: Unable to delete old records.');

It delete the record associated with the date 06/15/2003 and 06/18/ 2003. It should only delete the 06/15/2003 because it is two or more days old. The record with the 06/18/2003 date is not exactly two old yet.

I need a little assistance.

Oh yea, Kevin I checked the the MySQL manual. It does not give detailed explanation about combining these functions.