automatically deleting old records?

trying to delete old database entries

[distorto]

I'm sort of new to working with databases...I'm trying to write a script that automatically deletes old database entries. I thought this should work, but I keep throwing syntax errors. any ideas?

CREATE EVENT delete_old_mess ON SCHEDULE EVERY1 HOUR DO DELETE FROM 'messages' WHERE 'date' < NOW( ) - INTERVAL 2 DAY

[distorto]

sorry - forgot to mention - 'messages' is a table on a database called 'users' with a collumn called 'date'.
the table is fully populated with messages.

[distorto]

sorry for the backasswards post...
I'm realizing that maybe the problem lies in my naive assumption that the timestamp info in the date collumn is going to be understood by mysql. The 'date' collumn in the aforementioned table 'messages' contains timestamps. Since I'm comparing the dates in the collumn to mysql's internal date action, do I need to do something differently to get mysql to compare the dates?

[PHP_Chimp]

So are you using a php script to remove the old records or are you using a database event to remove the old records?
As you have posted some db code, so if you are using php then you may well need to post some of that code so we can see how it all goes together.

If you are using php then what event are you using to triger this clean up? Is it something that happens every time someone accesses the db, or do you have a script that is always running in the background and just has a time delay built into the code?

[distorto]

sorry. this is maybe more of an sql/database question. It is indeed a database event that deletes the old records.

[PHP_Chimp]

I would ask in the db area, you should get an answer faster.

[WesleyC]

One thing you might try is to do a "SELECT NOW() FROM messages" and see what exactly NOW() is giving you.

[distorto]

I found the problem. EVENT is an addition to mysql 5.1 and above. so it's still beta...which means I can't use it.
What else is out there for automatic archiving?
I want to delete messages over a month old every night or something like that.

[WesleyC]

Depending on the server OS, you could set up a cron job or a windows timed event (don't know the name of it). These are an event that occurs at a specific time on the server, and may be used to run a .php file via the command line (that's the simplest way). In that PHP file, run your DELETE query.

[distorto]

thanks for the reply. I did wind up using a cron job to run a php script that does what I need every day. seems like it will work fine.