It's a database, not a scripting language ... :)
You can run a simple cron entry like this:
0 4 * * * /path/to/mysql -u USER -pPASS -D DATABASE -e "delete from contacts
where TO_DAYS(CURDATE()) - TO_DAYS(today) >= 30 and
status != 'Y';"
so at 4 AM each day your SQL would be executed. For long SQL, you can write
it to a file and do something like so:
0 4 * * * /path/to/mysql -u USER -pPASS -D DATABASE < /path/to/myfile.sql
On 8/14/07, Beauford <php-list-user@stripped> wrote:
> > > I have been trying for days to find a simple command in
> > MySQL where I
> > > can automatically delete records based on some criteria after a
> > > certain timeframe.
> > >
> > > I found this in the MySQL manual, but I guess it only works with
> > > version 5.1+. Unfortunately the version I need this to work
> > on is 4.1,
> > > and can't be upgraded.
> > >
> > > CREATE EVENT e_hourly
> > > ON SCHEDULE
> > > EVERY 1 HOUR
> > > COMMENT 'Clears out sessions table each hour.'
> > > DO
> > > DELETE FROM something;
> > >
> > > Is there something similar in version 4.1 that will do the
> > same thing.
> > No. But there are cron jobs :-) And if you're deleting many
> > rows and you don't want to interfere with other running
> > queries (sounds like this is an OLTP system), try MySQL
> > Archiver with the --purge argument
> > (http://mysqltoolkit.sourceforge.net/). It's much more
> > efficient at large data volumes than a single DELETE statement.
> > Baron
> Really. I thought it would have some kind of scripting capability. I did
> check out the link, but really don't need anything that extensive.
> Is there a way to run the following command via cron.
> delete from contacts where TO_DAYS(CURDATE()) - TO_DAYS(today) >= 30 and
> status != "Y";
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/mysql?unsub=1