[SOLVED] How to remove entries in a DB that are a week old?

[techiefreak05]

I ahve a social network and I have a bulletin system, taht allows users to post bulletins that all their friedns can read... well anway, i dont want the bulletin to just sit there and take up DB space, how would i go about removing "bulletins" that are over a week old?

[per1os]

DELETE FROM tblName WHERE bulletincttime > (NOW() + (3600*24*7))

 

Something like that, however I would change it to select to test and make sure it runs right before you implement it as DELETE.

[techiefreak05]

would i change bulletincttime to the column where the bulletins posted date is stored in? and does it matter what form of the date() function i use???

[per1os]

It may matter what form of the date function. If it does not work like expected use PHP to determine the time and change it to:

 

$sql = "DELETE FROM tblName WHERE dateCol > " . (time()-(3600*24*7));

[techiefreak05]

Well I use this format of date() and take the $date variable and store it in the DB with the rest of the bulletin contents:

 

$date = date('F d, g:i');

[per1os]

$sql = "DELETE FROM tblName WHERE dateCol < " . date('F d, g:i', (time()-(3600*24*7)));

 

Edit: I think it should be lessthan not greater than.

 

[techiefreak05]

i wrote this but it isnt working. no error, but its not echoing the ids.

 

<?php
$query_x="SELECT * FROM `bulletins` WHERE `date` < " . date('F d, g:i', (time()-(3600*24*7)));
$result_x=mysql_query($query_x);
while($array_x=mysql_fetch_assoc($result_x)){
$test_x=$array_x['id'];
echo $test_x . "<br>";
}
?>	

[per1os]

<?php
$query_x="SELECT * FROM `bulletins` WHERE `date` < " . date('F d, g:i', (time()-(3600*24*7)));
$result_x=mysql_query($query_x) or die(mysql_error());
while($array_x=mysql_fetch_assoc($result_x)){
$test_x=$array_x['id'];
echo $test_x . "<br>";
}
?>	

 

Try that if not try this:

 

<?php
$query_x="SELECT * FROM `bulletins` WHERE `date` < (NOW() + (3600*24*7))";
$result_x=mysql_query($query_x) or die(mysql_error());
while($array_x=mysql_fetch_assoc($result_x)){
$test_x=$array_x['id'];
echo $test_x . "<br>";
}
?>	

 

Now if that does not work, try doing > instead of <

 

Date operations confuse me sometimes.

[techiefreak05]

the second code worked! thanks a lot frost!