Automatically Deleting Records

[hadoob024]

My web hosting company offers phpMyAdmin to administer MySQL dbs. My question is, is there anyway to have records that are older than X amount of time be deleted automatically? Or do I need to add some PHP code to my site to handle this? thanks.

[wickning1]

No, not really. Usually you set up a cron job (linux) for that, but if you are renting part of a shared server you often don't have that ability. You can set up a special web page and go there yourself, or you can put some code in one of your frequently used pages, and have it check the time to make sure it only runs the update every so often.

You might also consider just keeping the data. You can use MySQL's date math to ignore old data during page loads. Just add a "WHERE datecolumn > NOW() - INTERVAL 1 MONTH" to your queries, and put an index on datecolumn. There won't be a big performance hit this way. You may still want to go the delete route if the old data uses up an unusually large amount of space.

[hadoob024]

cool. thanks. yeah, i wasn't sure about it. i guess i'll have to go with plan b. i was thinking of adding a new field to my db that stores the timestamp. and then check this value in my script that performs searches before the section that performs the search. something like:

$postdate = time() - 2592000;
$timestampquery = mysql_query("DELETE FROM tablename WHERE dateadded < '$postdate'") or trigger_error('Error deleting records older than 30 days\r\n'.mysql_error(), E_USER_ERROR);


is that what you usually do?

[fenway]

Well, 5.1.x is supposed to have an event scheduler; of course, a regular CRON job could handle this quite easily as well. You should be wary of deleting "old" records -- it's bad practice to delete proof of things happening and/or existing. If space is not a premium, you can always use an activity field; otherwise, consider simply dumping this data to a backup table that you can keep off-site. The newer versions of MySQL actually support an ARCHIVE storage type ideally suited for this purpose.

[hadoob024]

Hmm. That does make sense. How difficult is the process of dumping the info to another table? Also, on a side note, what are your thoughts for storing images in a db? Because I will also need to think of how to handle the images associated with the listing information that I'm storing.

[fenway]

Not hard at all -- INSERT INTO... SELECT WHERE should just about do it. Or you could always dump raw SQL insert statements, depending on how you actually go about doing it. Then again, I'm pretty sure you can simply covert MyISAM to ARCHIVE with no data loss, so you could actually "instantly" archive the table, and then start fresh.

As far as images go, I'm weary of having a DB act like a filesystem for anything other than the simplest image storage system. That is, if each user can upload their own photo, then sure, make a new table call user_photos, and you're done. But if the app the DB is supporting is based on image uploads, then just store the file path and be done with it.

[hadoob024]

that makes sense. yeah. i didn't even consider something going wrong with just deleting old records. i just assumed that as long as the code was ok, and i thoroughly tested it, that i would be ok. i'm glad i decided on posting this topic. removing the item from the main table, and storing it in a archive/storage table definitely sounds like the way to go. probably saved me from a ton of headaches down the road.