Setting tables to expire?

[MaverickMT]

Hi. Basically, I'm using PHP + mySQL and I'd like to know if I can set tables to expire and delete themselves after a certain amount of time, or if I can achieve a similar effect.

Here's the situation. Whenever someone does a particular search on my site, a mySQL table will be made with some random # for a name, like "searchtable599" and the user will access their results by going to searchresults.php?s=599 or something. Now I just need to know if I can delete those mySQL tables after they've been used. Can anyone help me out? Thanks

[duniyadnd]

If you're familiar with cron or if your host provides it, you can use that (basically a unix timer which runs scripts on a timely basis).

Suggestion on how it can be implemented (though this is far from the best way of doing it, but I can't think striaght right now!! :P)

everytime you create a database, you create a file with the same name. The cron, using php, will search for all files in a directory, and finding it, checks the age of the file. If its created beyond the certain limit, run a function that deletes that file and table. else, read the rest of the files.

Or you could create a permanent table, which allocates the information for the newly created tables, having the newID and and timestamp of the table. You run the script asking for all rows (and subsequently tables) where timestamp > 4days to be deleted. Simpler and faster :D

Or if you worried about keeping a limited number of tables, you can ask a script to search the rest of the tables (like the one above) everytime someone goes through the search. Then its done manually.

Hope that helped...

[MaverickMT]

Basically my hurdle is just to get scripts that run on a timer and not when a page loads, and the rest would be OK. I'll try to research cron tonight; I've never heard of it. Any help pages or anything that you know of?

[duniyadnd]

http://www.ussg.iu.edu/usail/automation/cron.html