techiefreak05 Posted March 27, 2007 Share Posted March 27, 2007 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? Link to comment https://forums.phpfreaks.com/topic/44540-solved-how-to-remove-entries-in-a-db-that-are-a-week-old/ Share on other sites More sharing options...
per1os Posted March 27, 2007 Share Posted March 27, 2007 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. Link to comment https://forums.phpfreaks.com/topic/44540-solved-how-to-remove-entries-in-a-db-that-are-a-week-old/#findComment-216333 Share on other sites More sharing options...
techiefreak05 Posted March 27, 2007 Author Share Posted March 27, 2007 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??? Link to comment https://forums.phpfreaks.com/topic/44540-solved-how-to-remove-entries-in-a-db-that-are-a-week-old/#findComment-216336 Share on other sites More sharing options...
per1os Posted March 27, 2007 Share Posted March 27, 2007 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)); Link to comment https://forums.phpfreaks.com/topic/44540-solved-how-to-remove-entries-in-a-db-that-are-a-week-old/#findComment-216340 Share on other sites More sharing options...
techiefreak05 Posted March 27, 2007 Author Share Posted March 27, 2007 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'); Link to comment https://forums.phpfreaks.com/topic/44540-solved-how-to-remove-entries-in-a-db-that-are-a-week-old/#findComment-216351 Share on other sites More sharing options...
per1os Posted March 27, 2007 Share Posted March 27, 2007 $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. Link to comment https://forums.phpfreaks.com/topic/44540-solved-how-to-remove-entries-in-a-db-that-are-a-week-old/#findComment-216354 Share on other sites More sharing options...
techiefreak05 Posted March 27, 2007 Author Share Posted March 27, 2007 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>"; } ?> Link to comment https://forums.phpfreaks.com/topic/44540-solved-how-to-remove-entries-in-a-db-that-are-a-week-old/#findComment-216376 Share on other sites More sharing options...
per1os Posted March 27, 2007 Share Posted March 27, 2007 <?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. Link to comment https://forums.phpfreaks.com/topic/44540-solved-how-to-remove-entries-in-a-db-that-are-a-week-old/#findComment-216381 Share on other sites More sharing options...
techiefreak05 Posted March 27, 2007 Author Share Posted March 27, 2007 the second code worked! thanks a lot frost! Link to comment https://forums.phpfreaks.com/topic/44540-solved-how-to-remove-entries-in-a-db-that-are-a-week-old/#findComment-216386 Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.