Re: auto remove expired from database

Hello pacolvin,

If I am understanding you correctly you should be able to modify the delete function for your own purposes. The original post you linked to https://www.interactivetools.com/forum/forum-posts.php?postNum=2234367#post2234367 you will notice that there is a command looks like...

mysql_delete('schedule', null, " `date` < TIMESTAMP( DATE(NOW() - INTERVAL 2 HOUR) ) ");

Here the delete is being applied to the schedule table and it is looking at the date column to know what to delete... here it is any date older than 2 hours ago.

You can use this to delete records from any section by specifying the appropriate table and column to compare. Perhaps you have a table called "News" and a column called "post_date" and would like to delete anything older than 3 hours. You could again copy the function and replace the delete line with something like...

mysql_delete('news', null, " `post_date` < TIMESTAMP( DATE(NOW() - INTERVAL 3 HOUR) ) ");

Obviously this would have to be run against each table (with the table name, the column and the time interval specified accordingly). Perhaps even write the function to take an array of table names, their columns and the interval. It could look something like this (this has not been fully tested, so try on dummy data or dev site)...

// An array full of the tables, columns and hours back you want to delete from
$sections = array(
    array('table' => 'schedule', 'column' => 'date', 'hours_back' => 2),
    array('table' => 'news', 'column' => 'post_date', 'hours_back' => 3),
    array('table' => 'other_table', 'column' => 'other_date', 'hours_back' => 4)
);

function delete_old_records(array $sections) {
    // Move through each table
    foreach ($sections as $section) {
        // Delete old records
        mysql_delete($section['table'], null, "`{$section['column']}` < TIMESTAMP(DATE(NOW()-INTERVAL {$section['hours_back']} HOUR))");
        echo "Old records for {$section['table']} deleted!";   
    }
}

IMPORTANT: As recommended in the earlier post, be sure to backup all your tables before performing such commands.

I hope this answers your question or at least points you in the right direction!

Thanks!

Tim,

Thanks for the reply.  I'll work on making some changes to the function and see how it works.  The array will work fine for multiple sections.

Thanks

Phil