Jump to content

database item expire after certain amount of time


Cflax

Recommended Posts

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.