Suggestions on ways to remove expired cookie items from DB

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
dardsemail
Forum Contributor
Posts: 136
Joined: Thu Jun 03, 2004 9:02 pm

Suggestions on ways to remove expired cookie items from DB

Post by dardsemail »

Hi,

I have a shopping cart application that allows persistent carts based on 30 day expiration times for cookies. My question is whether anyone can point me to any good tutorials on the most efficient and effective way to remove items from the shopping cart based on expired cookies.

I've been looking online through Google, but can't seem to find anything (but - I'm willing to admit that I'm likely not looking in the right places!).

Thanks!
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

have you tried something like:

Code: Select all

DELETE FROM tablename WHERE last_time < NOW() - $expiry
?
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

You can't do it through the cookies, because expired cookies simply don't exist.

Feyd's way would work, maybe running through a crontab would be best.
dardsemail
Forum Contributor
Posts: 136
Joined: Thu Jun 03, 2004 9:02 pm

Post by dardsemail »

Since I'm new to this, I'm going to try and fumble my way through seeing if I understand...

I don't currently have an expiry date field in my table - but I'm assuming that I'd need to add one.

So, if the cookie didn't exist anymore, where would I add the line removing the expired cookie? Also, how do I extract the expiry date from the cookie information?

Again - totally new to this, so I'm fumbling my way through. Is there a decent tutorial anywhere that might help?
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

Basically, a cookie has an expiration date (which you set when you create it), and when that date is up, it is deleted automatically.

To get rid of database objects more that X days old, do what Feyd suggested - change your database to include some form of date, then, perhaps once a day, you can have the database delete any rows that have been idle for X days.
Grim...
DevNet Resident
Posts: 1445
Joined: Tue May 18, 2004 5:32 am
Location: London, UK

Post by Grim... »

Now I think about it, this is possible to do automatically.

Create two cookies per user, one with a 30 day 'life', and one with a 5 year life, like so:

Code: Select all

<?php
setcookie("cookie1", "cookievalue", time()+60*60*24*30); //30 day cookie
setcookie("cookie2", "cookievalue", time()+60*60*24*365*5); //5 year cookie
// both have the same value
?>
Now, when a user comes to your site, you can check to see if they have only $cookie2 set, and, if they do, you can delete the database rows, like so:

Code: Select all

<?php
if (!$cookie1 AND $cookie2)
{
    mysql_query( "DELETE FROM yourtable WHERE userid = '$cookie2'", $link ) or die ( "My SQL Error:<br><i>$query</i><br><b>".mysql_error() );
    
    //and delete cookie2?
    setcookie("cookie2", "", time()-60*60);
}
?>
However, for this to work the user would have to visit your site again, so Feyd's original way is best.
EricS
Forum Contributor
Posts: 183
Joined: Thu Jul 11, 2002 12:02 am
Location: Atlanta, Ga

The actual deletion

Post by EricS »

After you've created an expiration date field in the database then you have to actually delete it on a regular basis.

I use two different ways to accomplish this.

1. If you have shell access to the server, you can set a cron job to run a script every day that deletes all rows where the expiration date is more than 30 days old.

2. If you don't have access. Then execute a sql command when someone checks out that, as well as processing the order, then cleans the database by removing all expiration dates over 30 days old.

Better to put the cleaning code in the check out process rather than in something like the product listing pages. Keeps the database overhead lower.
dardsemail
Forum Contributor
Posts: 136
Joined: Thu Jun 03, 2004 9:02 pm

Post by dardsemail »

Hi,

Thanks everyone - I don't have Shell access, so I'll have to use the check-out option.

I'll give it a shot. I suspect you'll be hearing from me again ;-)

Thanks again!
dardsemail
Forum Contributor
Posts: 136
Joined: Thu Jun 03, 2004 9:02 pm

Post by dardsemail »

...and you are! I'm finally getting around to cleaning this up and realized that I have absolutely no clue as to how to extract the expiry date for the cookie so that I can enter it into the database as well as use it for comparison at a later date!

Can someone please help?
Post Reply