July 20th, 2000, 04:19 PM
Does anybody know how to make a script that automatically removes mySQL records older than 14 days?
July 20th, 2000, 04:55 PM
I'm making an assumption that you're using some flavor of UNIX, you have an account on the system, and the mysql table has some sort of date/timestamp field.
1) create a file called sqlexec containing the following:
mysql -u your_username -pyour_password
2) create a file called delete.sql containing the following:
delete from table_name where to_days(now())-to_days(date_field) > 14;
3)run it as a crontab:
# crontab -e
(insert into crontab file)
2 50 * * * /path/to/file/sqlexec<delete.sql
This will execute the script once a day at 2:50 AM
If the above assumptions weren't correct, then there are other ways, but that's the Q&D method for *NIX.
July 20th, 2000, 04:56 PM
That depends, do you have a field in the table that stores the date?
mysql_query("delete from table_name where to_days(datesub(now(), interval 14 day)) > to_days(date_field)");
You can set this up in a cron job to run after midnite.
July 20th, 2000, 05:23 PM
I just have one more question. In witch format shall the date be? Should I use:
Or should I use the mySQL timestamp field?
July 21st, 2000, 01:52 PM
You need to use some type of mysql date/time field. Don't use a unixtimestamp if you want to use mysql's built in time functions. However, you don't necessarily want to use the timestamp datatype as that will automatically change whenever you make an insert/update/replace on the record. If you don't want that use a date or datetime (if you need the time recorded as well as the date).