database item expire after certain amount of time

[Cflax]

did some searching on the internet and didnt find much.

i have created an online classifieds website and want all created listings to expire and delete after a certain amount of days.

honestly i have no idea where to start with this implementation. cookies were the only thing that even crossed my mind...

any ideas?

btw each listing is stored in a table called ob_listings

[JKG]

cron job everyday to loop through all rows and delete if timestamp is x old?

[The Little Guy]

I wouldn't loop through, that would/could take too much time.

 

I would first select all the rows in the table that are a certain age, for example (10 = 10 days):

delete from ob_listings where datediff(now(), ob_listing_date) > 10

So this would delete anything that is 11 or more days old.

 

Personally, I would recommended not deleting the rows, I would just update a column that says whether the row is active or not, so I probably would do this:

update ob_listings set is_active = 0 where datediff(now(), ob_listing_date) > 10

 

now when you want to get something from the database you just do this:

select * ob_listings where is_active = 1

[perky416]

Hi Cflax,

 

I had the same problem as you, i didnt want to use a cron job so I created a piece of code similar to the following, and i use include() to include it in every page, that way every time a page is loaded it checks the database to see if a listing has expired, then deletes it as required.

 

$query = mysql_query("SELECT * FROM ob_listings");
while ($row = mysql_fetch_assoc($query))
{
mysql_query("DELETE FROM ob_listings WHERE NOW() > expiry_date");		
}

 

expiry_date is the table field that contains the expiry date.

 

Hope it helps

[perky416]

I wouldn't loop through, that would/could take too much time.

 

I would first select all the rows in the table that are a certain age, for example (10 = 10 days):

delete from ob_listings where datediff(now(), ob_listing_date) > 10

So this would delete anything that is 11 or more days old.

 

 

Very good point, i may use this myself.