Automatically delete a record in db after a certain date

[ldoozer]

Hi all,

I have sports activities site where people log activites and then the public can search for activities in the database.
So that the data is kept current, I would like activities with end dates that have passed to automaticaly delete themselves.
Does anyone have an idea how to go about this.

Thanks in advance for your input
Steve

[Psycho]

By deleting the records you will never be able to access that data. I prefer to always have a history of any "date" events. Wouldn't it be easier to just limit the search results to current events? I.e. WHERE data > specifiedDate

[micmania1]

[quote author=mjdamato link=topic=124195.msg514251#msg514251 date=1169841269]
By deleting the records you will never be able to access that data. I prefer to always have a history of any "date" events. Wouldn't it be easier to just limit the search results to current events? I.e. WHERE data > specifiedDate
[/quote]

If this option isn't for you, you can use cronjobs which i have no experience with so you'll have to google it. Make sure your host provides cronjobs before you do this or your probably wasting your time.

[Psycho]

If cron jobs are not available to you, and you still want to delete, you could add a single step to your search script to first delete any records older than a specified date, then do the search. Not efficient by any means, but on a site that does not have a large usage it is very manageable.

Or, you could create a small function to run before doing the search. The function could check to see if a particular log file has today's date. If so, then it continues with the search. If not, it performs the deletion of old records, then opens the log files and adds an entry to that effect. Doing this, the deletion will occur only on the first search each day.

[cmgmyr]

if you really want to delete something you can use something like this:[code]$query = ("DELETE FROM your_table WHERE (TO_DAYS(NOW()) - TO_DAYS(date)) > 30") or die ("Error in Update sql: ". mysql_error());
$result = mysql_query($query) or die('Query failed: ' . mysql_error());[/code]

This will delete anything that is over 30 days old

[ldoozer]

Thanks guys, this is all really usefull. I don't think I will need to archive the activities so deleteing them is fine.
One thing i was struggling with is the best time to perform the deletions.
Not all my activities have end dates as some are ongoing. Could someone explain if this would cause a problem when running the below because i dont fully understand the query. If it helps my table is called publicActivities and the field in question is called  ActEndDate (varchar) which would contain either a something like 01/01/2008 or emty if no end date.


[code]$query = ("DELETE FROM your_table WHERE (TO_DAYS(NOW()) - TO_DAYS(date)) > 30") or die ("Error in Update sql: ". mysql_error());
$result = mysql_query($query) or die('Query failed: ' . mysql_error());[/code]

[Psycho]

What is the rule you want for deletions? Delete records older than 30 days, delete if the date has passed, ???

[ldoozer]

Delete if the date has passed.

[Psycho]

This should work:
[code]
$query = "DELETE FROM publicActivities WHERE (TO_DAYS(NOW()) - TO_DAYS(ActEndDate)) > 0";
mysql_query($query) or die('Query failed: ' . mysql_error());
[/code]

However, be sure to test any delete script on sample data before actually using it.

[ldoozer]

Can you confirm that this would only work if the field was a date field. Cos unfotunatly the ActEndDate field is a varchar :(