By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,521 Members | 1,420 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,521 IT Pros & Developers. It's quick & easy.

delete expired queries

P: n/a
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?

May 6 '07 #1
Share this Question
Share on Google+
2 Replies


P: n/a
>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?
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.

May 6 '07 #2

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

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

//check desired information bla bla

$qry = "INSERT INTO `bul_data` (`DATE`, `TITLE`,
`DEPARTMENT`,`CAMPUS`, `LINK`) VALUES (DATE_ADD('$datetime', INTERVAL
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='',
LINK='$link'";
//auto delete expired queries
$qry2 = "DELETE FROM `bul_data` WHERE `DATE` LIKE '{$title_date}' >
SUBDATE('$datetime', INTERVAL 1 DAY)";
$res = mysql_query($qry) OR die(mysql_error());

mysql_query($qry);

echo "New bulletin have been stored.";

mysql_query($qry1);

echo "Records have been updated.";

mysql_query($qry2);

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'.

May 7 '07 #3

This discussion thread is closed

Replies have been disabled for this discussion.