netpants Posted March 21, 2007 Share Posted March 21, 2007 Ok is there some snipet of code I can throw in with my cron to delete news posts that are more than "X" days old, X being the number of my choice? I did a search but was probably searching wrong phrases as I didnt find anything but one post, and that post did not have an answer. Quote Link to comment Share on other sites More sharing options...
per1os Posted March 21, 2007 Share Posted March 21, 2007 Is this going to be part of the query? If so it should be something like this. <?php $x=5; $oldTime = mysql_query("DELETE FROM table WHERE entereddate < " .time()-3600*24*$x); ?> Now I am not sure if it should be greater than or less than < or > so before you go and do mass quantities test on a test database. Quote Link to comment Share on other sites More sharing options...
netpants Posted March 21, 2007 Author Share Posted March 21, 2007 /* Delete news items */ mysql_query("DELETE FROM newsinf WHERE mseen=1", $db); ?> THat is what I currently have, so its deleting stuff when the cron is run only if it has been read,so I need to change that to instead delete only if its X days old, how would I incorporate what you have stated into what I have. Quote Link to comment Share on other sites More sharing options...
per1os Posted March 21, 2007 Share Posted March 21, 2007 What is your table structure? Quote Link to comment Share on other sites More sharing options...
netpants Posted March 21, 2007 Author Share Posted March 21, 2007 Field Type Collation Attributes Null Default Extra Action rtime int(17) No 0 Browse Change Drop Primary Index Unique Fulltext ntype tinyint(1) No 1 Browse Change Drop Primary Index Unique Fulltext mseen tinyint(1) No 0 Browse Change Drop Primary Index Unique Fulltext tox int(12) No 0 Browse Change Drop Primary Index Unique Fulltext toy tinyint(2) No 0 Browse Change Drop Primary Index Unique Fulltext toz tinyint(2) No 0 Browse Change Drop Primary Index Unique Fulltext message text latin1_swedish_ci Yes NULL Browse Change Drop Primary Index Unique Fulltext nid bigint(20) No auto_increment Browse Change Drop Primary Index Unique Fulltext Quote Link to comment Share on other sites More sharing options...
per1os Posted March 21, 2007 Share Posted March 21, 2007 So rtime must be the created time. Try this: <?php $x=5; $oldTime = mysql_query("DELETE FROM newsinf WHERE rtime < " .time()-3600*24*$x); ?> Like I said, I am unsure if it should be > or < so test it out on a test DB or test it out using this: <?php $x=5; $oldTime = mysql_query("SELECT nid, rtime FROM newsinf WHERE rtime < " .time()-3600*24*$x); while ($row = mysql_fetch_array($oldTime)) { print $row['nid'] . "-ID : " . date('Y-m-d', $row['rtime']) . "-Date to be deleted<br />"; } ?> Make sure it is deleting the right data. If not switch the < to > and test it again. Quote Link to comment Share on other sites More sharing options...
netpants Posted March 21, 2007 Author Share Posted March 21, 2007 All I get is this Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/battle/domains/battlefordomination.com/public_html/removedDIRECTORY/newstickerxyz.php on line 13 Here is the full .php <?php $db = mysql_connect("localhost","db","pw"); mysql_select_db("battlenew", $db); $bDelete = false; ?> <?php $x=5; $oldTime = mysql_query("SELECT nid, rtime FROM newsinf WHERE rtime < " .time()-3600*24*$x); while ($row = mysql_fetch_array($oldTime)) { print $row['nid'] . "-ID : " . date('Y-m-d', $row['rtime']) . "-Date to be deleted<br />"; } ?> Quote Link to comment Share on other sites More sharing options...
per1os Posted March 21, 2007 Share Posted March 21, 2007 <?php $x=5; $oldTime = mysql_query("SELECT nid, rtime FROM newsinf WHERE rtime < " .time()-3600*24*$x) OR DIE(mysql_error()); while ($row = mysql_fetch_array($oldTime)) { print $row['nid'] . "-ID : " . date('Y-m-d', $row['rtime']) . "-Date to be deleted<br />"; } ?> What error message do you get? Quote Link to comment Share on other sites More sharing options...
netpants Posted March 21, 2007 Author Share Posted March 21, 2007 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-432000' at line 1 Quote Link to comment Share on other sites More sharing options...
per1os Posted March 21, 2007 Share Posted March 21, 2007 <?php $x=5; $oldTime = mysql_query("SELECT nid, rtime FROM newsinf WHERE rtime < '" .time()-3600*24*$x."'") OR DIE(mysql_error()); while ($row = mysql_fetch_array($oldTime)) { print $row['nid'] . "-ID : " . date('Y-m-d', $row['rtime']) . "-Date to be deleted<br />"; } ?> Try that. Quote Link to comment Share on other sites More sharing options...
netpants Posted March 21, 2007 Author Share Posted March 21, 2007 Yea its the same error. Quote Link to comment Share on other sites More sharing options...
per1os Posted March 21, 2007 Share Posted March 21, 2007 <?php $x=5; $oldTime = mysql_query("SELECT nid, rtime FROM newsinf WHERE rtime < unix_timestamp(now() - (3600*24*".$x."))") OR DIE(mysql_error()); while ($row = mysql_fetch_array($oldTime)) { print $row['nid'] . "-ID : " . date('Y-m-d', $row['rtime']) . "-Date to be deleted<br />"; } ?> Try that. Quote Link to comment Share on other sites More sharing options...
netpants Posted March 21, 2007 Author Share Posted March 21, 2007 Ok it worked with no errors, but now to just test it if it actually deletes old read posts. Quote Link to comment Share on other sites More sharing options...
netpants Posted March 21, 2007 Author Share Posted March 21, 2007 So this is going to delete everything, is it possible to add that mseen=1 so it only deletes stuff that has been read. Quote Link to comment Share on other sites More sharing options...
per1os Posted March 21, 2007 Share Posted March 21, 2007 Yep, just do an AND statement. Quote Link to comment Share on other sites More sharing options...
netpants Posted March 21, 2007 Author Share Posted March 21, 2007 so it would be like this? <?php $x=1; $oldTime = mysql_query("SELECT nid, rtime FROM newsinf WHERE rtime < unix_timestamp(now() - (3600*24*".$x.") AND mseen=1)") OR DIE(mysql_error()); while ($row = mysql_fetch_array($oldTime)) { print $row['nid'] . "-ID : " . date('Y-m-d', $row['rtime']) . "-Date to be deleted<br />"; } ?> Quote Link to comment Share on other sites More sharing options...
per1os Posted March 21, 2007 Share Posted March 21, 2007 You do not need the extra paran after mseen =1, that will cause an error $oldTime = mysql_query("SELECT nid, rtime FROM newsinf WHERE rtime < unix_timestamp(now() - (3600*24*".$x.")) AND mseen=1") OR DIE(mysql_error()); That should do her. Quote Link to comment Share on other sites More sharing options...
netpants Posted March 21, 2007 Author Share Posted March 21, 2007 Great thanks, I will come back and update for this issue once I see if it works after a day. Quote Link to comment Share on other sites More sharing options...
netpants Posted March 22, 2007 Author Share Posted March 22, 2007 After waiting a day to find out if this script works properly, I have found that it does not remove any news. Any more Ideas? Quote Link to comment Share on other sites More sharing options...
netpants Posted March 22, 2007 Author Share Posted March 22, 2007 Ok i changed the < to > and got it to print out the following, so somthing is working correctly its just not deleteing. 475-ID : 2007-03-22-Date to be deleted 476-ID : 2007-03-22-Date to be deleted 477-ID : 2007-03-22-Date to be deleted 478-ID : 2007-03-22-Date to be deleted 479-ID : 2007-03-22-Date to be deleted 481-ID : 2007-03-22-Date to be deleted 482-ID : 2007-03-22-Date to be deleted 483-ID : 2007-03-22-Date to be deleted 484-ID : 2007-03-22-Date to be deleted 486-ID : 2007-03-22-Date to be deleted 488-ID : 2007-03-22-Date to be deleted 489-ID : 2007-03-22-Date to be deleted 490-ID : 2007-03-22-Date to be deleted 491-ID : 2007-03-22-Date to be deleted 492-ID : 2007-03-22-Date to be deleted 493-ID : 2007-03-22-Date to be deleted 494-ID : 2007-03-22-Date to be deleted 495-ID : 2007-03-22-Date to be deleted 496-ID : 2007-03-22-Date to be deleted 497-ID : 2007-03-22-Date to be deleted 498-ID : 2007-03-22-Date to be deleted 499-ID : 2007-03-22-Date to be deleted 500-ID : 2007-03-22-Date to be deleted 501-ID : 2007-03-22-Date to be deleted 502-ID : 2007-03-22-Date to be deleted 503-ID : 2007-03-22-Date to be deleted 504-ID : 2007-03-22-Date to be deleted 505-ID : 2007-03-22-Date to be deleted 506-ID : 2007-03-22-Date to be deleted 507-ID : 2007-03-22-Date to be deleted 509-ID : 2007-03-22-Date to be deleted 514-ID : 2007-03-22-Date to be deleted 520-ID : 2007-03-22-Date to be deleted 522-ID : 2007-03-22-Date to be deleted 542-ID : 2007-03-22-Date to be deleted 543-ID : 2007-03-22-Date to be deleted 547-ID : 2007-03-22-Date to be deleted 563-ID : 2007-03-22-Date to be deleted 564-ID : 2007-03-22-Date to be deleted 567-ID : 2007-03-22-Date to be deleted 568-ID : 2007-03-22-Date to be deleted 569-ID : 2007-03-22-Date to be deleted 571-ID : 2007-03-22-Date to be deleted This is my current code <?php $db = mysql_connect("localhost","user","pw"); mysql_select_db("database", $db); $bDelete = false; ?> <?php $x=1; $oldTime = mysql_query("SELECT nid, rtime FROM newsinf WHERE rtime > unix_timestamp(now() - (3600*24*".$x.")) AND mseen=1") OR DIE(mysql_error()); while ($row = mysql_fetch_array($oldTime)) { print $row['nid'] . "-ID : " . date('Y-m-d', $row['rtime']) . "-Date to be deleted<br />"; } ?> Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.