delete expired queries

Do you have a question? Post it now! No Registration Necessary.  Now with pictures!

Threaded View
I had queries stored in mysql database according to queries
publication date (in format 2007-5-6, 2007-4-30 etc.). However, i
would like to implement automatic expired queries deletion after 6
months. For instance, 2007-4-30 released bulletin will be deleted
after it expired 6 months later. How do I peform this?

Re: delete expired queries

Quoted text here. Click to load it

DELETE FROM publications WHERE pubdate < subdate(now(), INTERVAL 6 MONTH);

This presumes that pubdate is a DATE or DATETIME column, or can be
converted to one (say, with strtotime()).

Run this query using the mysql command-line utility from cron or some
regularly scheduled job, daily or weekly or whatever.

Re: delete expired queries

I'm extracting web data from dynamically generated RSS. So, my coding
generally looks like this:

    $datetime = date("Y-n-j");

    //check desired information bla bla

    $qry = "INSERT INTO `bul_data` (`DATE`, `TITLE`,
1 DAY),'$title','$category','', '$link')";

    //auto update queries
    $qry1 = "UPDATE `bul_data` SET DATE=DATE_ADD('$datetime', INTERVAL 1
DAY), TITLE='$title', DEPARTMENT='$category', CAMPUS='',

    //auto delete expired queries
    $qry2 = "DELETE FROM `bul_data` WHERE `DATE` LIKE '' >
SUBDATE('$datetime', INTERVAL 1 DAY)";
    $res = mysql_query($qry) OR die(mysql_error());


    echo "New bulletin have been stored.";


    echo "Records have been updated.";


    echo "Expired bulletin have been deleted.";


However, it doesn't work to insert desired information into my
database. It works only if I omit the DATE_ADD() function and replace
it with '$datetime'.

Site Timeline