Atomatically remove entries after X number of days

PHP
1

I have a script that basically displays some posted ads.

I would like to add a feature that automatically removes the posts after X number or days(or after a time). Let’s say they will stay up 7 days and after that they will be removed.

I’m not sure how to add this to my current code.

Would be grateful for some input about this.

When someone posts an ad, the date is inserted into a datetime field in this format(2010-01-02 09:28:22)

Here is part of the code. First just the select statement and then the display of the rows.

I just wonder how and where to insert a DELETE FROM code in all this



//SELECT STUFF

$QUERY10 = mysql_query("SELECT
student_id
, s_date
, s_firstname
, s_lastname
, LEFT(s_content, 40)  
FROM students");

$NUMROWS10 = @mysql_num_rows($QUERY10);

if (!$NUMROWS10) {
echo "NO RESULTS";

} else {


//CAN THE DELETION CODE BE HERE?


$I10 = 0;
while ($I10 < $NUMROWS10) {

$student_id = mysql_result($QUERY10,$I10,"student_id");
$s_date = mysql_result($QUERY10,$I10,"s_date");
$s_firstname = mysql_result($QUERY10,$I10,"s_firstname");
$s_lastname = mysql_result($QUERY10,$I10,"s_lastname");


//DISPLAY ROWS HERE

$I10++;

}
}
2

Hi

If the ads only have to appear for seven days you only really need the date type. You could use a MySQL Query to get rid of the unwanted ads like this

$sql=“DELETE FROM students WHERE DATE_SUB(CURDATE(),INTERVAL 7 DAY) <= s_date”;

Personally, I would make a field called display as an ENUM Field with default Y and coices of ‘Y’,‘N’ and run a query to change the display properties to N that way you dont delete your ads. It seems so final! and you cant get tem back.

You cold run a cleanup query later to remove older than say a month

Hope that helps

3

Does it get more complicated in the SQL query if you use “datetime” instead of “date” ?

4

Hi

I beleive you can still do the query based on date time instead of just date.

You my want to look at http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html#function_date-add

Thats a pretty good plce to start.

Is there a reason you want to use time also? is the time of day important or do you have a lot of daily activity.

My motto has always been why make it any ore complicated than it needs to be.

:slight_smile:

Keith

5

I actually used to have date instead of datetime, but I think I changed to datetime because there was some problem with sorting after date. When I used date it didn’t sort correctly. I would like the ads to be sorted after the time they are posted.

Do you know if there is a problem with date and sorting?