Page 1 of 1
Suggestions on ways to remove expired cookie items from DB
Posted: Sun Jun 27, 2004 9:21 pm
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!
Posted: Sun Jun 27, 2004 9:33 pm
by feyd
have you tried something like:
Code: Select all
DELETE FROM tablename WHERE last_time < NOW() - $expiry
?
Posted: Mon Jun 28, 2004 5:35 am
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.
Posted: Mon Jun 28, 2004 10:12 am
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?
Posted: Mon Jun 28, 2004 10:19 am
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.
Posted: Mon Jun 28, 2004 10:25 am
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.
The actual deletion
Posted: Mon Jun 28, 2004 10:34 am
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.
Posted: Mon Jun 28, 2004 2:32 pm
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!
Posted: Thu Aug 26, 2004 8:54 pm
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?