Delete Row after x amount of time

[cwd]

I have a classifieds page setup and would like to delete users posts after 30 days from the original post date.

I was wondering how I should set this up.

I’ve tried both of these attempts, but no luck getting it to work.

1.

[php] $classifieds = DB::getInstance()->query(“SELECT id, date, username, title, description FROM classifieds”);

$classifieds = DB::getInstance()->query(“DELETE FROM classifieds WHERE date > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MINUTE)”);[/php]

2.

[php]$classifieds = DB::getInstance()->query(“SELECT id, date, username, title, description FROM classifieds WHERE date > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MINUTE)”);[/php]

For testing I want to delete after 1 minute so I know it’s working. Once it’s working I plan to change to 30 DAY in the query.

Any help is appreciated.

[richei]

You need to run the script from a cronjob for to automatically do it.

First, date is a reserved word, you’ll need to change it to something else and second, you’re missing a ), try (change dates to whatever date is changed to):
[php]$classifieds = DB::getInstance()->query(“DELETE FROM classifieds WHERE dates > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 1 MINUTE))”);[/php]

[cwd]

Hmm. that worked but deleted every row in the table which I expected, but I set it to 10 minutes and added a new post and it doesn’t add the post, just deletes it right away…?

Thanks for the date to dates tip btw.

[richei]

Did you put MINUTES or MINUTE? Leave it as MINUTE.

[cwd]

It MINUTE

Here is my code: Note the main query is pulling from the pagination script.

[php]<?php include_once($_SERVER['DOCUMENT_ROOT'] . "/functions/pagination-classifieds.php"); ?>

<?php $classifieds = DB::getInstance()->query("DELETE FROM `knollsclassifieds` WHERE `dates` > UNIX_TIMESTAMP(DATE_SUB(NOW(), INTERVAL 10 MINUTE))"); ?> <?php foreach($classifieds->results() as $c){ ?>

<?php echo escape($c->title); ?>

<?php echo escape (date("M. d, Y", strtotime ($c->dates))); ?> - Posted by: <?php echo escape($c->username); ?>

.articletext img{width:100%; height:auto;}<?php echo ($c->description);?>

<?php

}
?>
[/php]

[richei]

Assuming all u changed was the interval, it should work. It could be that 10 minutes hadn’t gone by just yet, but it should have by now.

[cwd]

At 1 Minute I would post check my admin and see that it posted then go to the page and it would be gone. At 10 minutes it just posts and delete right away.

I’m doing this on local using xampp. Could that be an issue? Pretty new to this CRON stuff

[richei]

You’re not using a cronjob, you wouldn’t need to use the where clause to set the interval if you were, because you’d just set the interval with the cronjob.

The script is running every time you run the page, but still, it shouldn’t delete the row right away. I think you’re using the wrong timestamp though, you should be using the created on date from the ad. You first need to get that timestamp, then compare that against now.

[cwd]

Here is how i have dates setup in my table (see attached image.)
Should I change to a different timestamp???

---

[richei]

Na, I wouldn’t change it. The time stamp is already in a unix format, you don’t need to tell it what it is in the query. You also don’t want to take time away from it, you need to add 10 minutes, after all, you need to know what the timestamp will be 10 minutes into the future, not in the past. DATE_SUB subtracts from, DATE_ADD adds to.

Try
$classifieds = DB::getInstance()->query(“DELETE FROM knollsclassifieds WHERE dates > DATE_ADD(dates, INTERVAL 10 MINUTE)”);

[cwd]

Hmmm. That didn’t do the trick either… Maybe it has something to do with my pagination script.
Here is my pagination script query:

[php]<?php

$adjacents = 2;

$classifieds = DB::getInstance()->query("SELECT id, dates, username, title, description FROM knollsclassifieds");

$records = $classifieds->Count();
$targetpage = basename($_SERVER[‘PHP_SELF’]);
$limit = 2;
$page = isset($_GET[‘page’])? $_GET[‘page’] : 1;
if ($page)
$start = ($page - 1) * $limit;
else
$start = 0;
$classifieds = DB::getInstance()->query(“SELECT * FROM knollsclassifieds ORDER BY id DESC LIMIT $start, $limit”);

if ($page == 0)
$page = 1;…
[/php]

Then here is the classifieds.php page and how its structured:

[php]<?php include_once($_SERVER['DOCUMENT_ROOT'] . "/functions/pagination-classifieds.php"); ?>

<?php $classifieds = DB::getInstance()->query("DELETE FROM `knollsclassifieds` WHERE `dates` > DATE_ADD(`dates`, INTERVAL 10 MINUTE)"); ?> <?php foreach($classifieds->results() as $c){ ?>

<?php echo escape($c->title); ?>

<?php echo escape (date("M. d, Y", strtotime ($c->dates))); ?> - Posted by: <?php echo escape($c->username); ?>

.articletext img{width:100%; height:auto;}<?php echo ($c->description);?>

<?php

}
?>
[/php]

It doesn’t matter where this query is placed on the page does it?
[php]<?php $classifieds = DB::getInstance()->query("DELETE FROM `knollsclassifieds` WHERE `dates` > DATE_ADD(`dates`, INTERVAL 10 MINUTE)"); ?>[/php]

I guess if this doesn’t work another way to go would be to only display classified ads under 30 days old. Then on my admin section I could display all ads over 30 days old and delete them manually. Not sure how to write that in the query though…? I will do some research on that.

I appreciate your help. Let me know if you have any ideas.

[richei]

If you want it to show up after submission, then it needs to be on top of the page. And you’ll need to run the script after 10 minutes since its not a cronjob.

You want all ads deleted that older than 10 minutes right? i’ll setup a test page to see if i can figure this out.

[cwd]

Yes for testing purposes I would like the ads deleted after 10 - 2 minute, just a short amount of time, I don’t want to wait forever to see if it’s working. Once its working I would like to change it to 30 days

i'll setup a test page to see if i can figure this out.

That would be awesome and very kind of you. Let me know if you need any of my files or an sql dump of my table.

Thanks again.