Forum Moderators: coopster
What i want todo is have a product added to the database via the customer but i want that product to expire in 7 days time and automatically delete it self from the database so it does not show any more.
i would like the 7 days to be a varible that i can change for example 14 days etc. Has any one got an easy way todo this? thanks for looking and hope its snowing wear you are.
merry xmas
a) Schedule a job on your system to run the delete query every day.
b) Run the delete query immediately before your select query.
However you trigger it; deleting records over a certain age is easy; all you need to do is store a created column as a timestamp in your table and use the query:
DELETE FROM products WHERE created < $test
...where $test is a value calculated to be the timestamp value of 7 or 14 or whatever days ago from now - which you can get using PHP's time() [uk.php.net] function.
A timestamp is based on the number of seconds since some time a long time ago; and as there are 86400 seconds in a day you can calculate the $test value as:
$max_age = 7;
$test = (time()-($max_age * 86400));
When you insert a new product, simply set the created field to the current value of time().
Hope this gives you something to go on! And there's no snow here at the moment... :(
im a little confused here - 86400 x 7 = 604800
but when my script is run it outputs this
<?php
$max_age = 7;
$test = (time()-(7 * 86400));
echo $test;
?>
1103395874
why is this im confused?
$exp = 7*24*60*60; //seconds for 7 days
$t = time() + $exp;
if (($row['Timestamp'] - $t) < 0) {
//do delete
}
I'd explain it, but I'm so full of Christmas booze I'd probably make a hash of it. I'll come back tomorrow, unless someone wants to take over the explanation! Heh. Merry Xmas!
$delete_date = date("Y-m-d", strtotime("-7 days"));mysql_query("DELETE FROM table WHERE date = '$delete_date'") or die(mysql_error());
Simple enough.
dc :)
get current date
then get var (either be 7 or 14 days etc right up to 56 days)
i then want to add the amount of days to the current date.
then i want to add this to the database and every day i will run a script that deletes records with the current dat. so that in 7 days time i will run script and it will delete the record that was added 7 days ago.
Does this make more sense?
regards adam
There is a shortcoming in the technique you describe, in that you utterly rely on your deletion script running every day in order to remove old products. It is far safer from a data integrity point of view to delete using an "IS LESS THAN" clause, because then you are guaranteed to catch up even if your delete script fails to run occasionally. You can do exactly what you want as follows:
1. Add an INT column called "created" to your product table
2. When you insert a new product into your database, use code similar to the following:
$product_name = "Some Product";
$created = time();
$sql = "INSERT INTO products SET name='$product_name',created='$created'";
3. Now, you can run the following every day:
$max_age = 7; // number of days you wish a product to stay in the database
$test = (time() - (86400 * $max_age));
$sql = "DELETE FROM products WHERE created < '$test'";
That's all there is to it. If time() is returning the wrong date on your system, the first place to look is your system's clock...!
ie
$date = date ("Y-m-d", time () + (7*86400)); //Last week's time
echo $date;
what u think
i havea field in my table called expire which has a date for today.
this is my script
<?php
// Show date on the page.
$today = date ("d-m-Y", time () + (0*86400)); //todays date
echo $today;
//connect to MySQL; note we've used our own parameters- you should use
//your own for hostname, user, and password
$connect = mysql_connect("a", "a", "a");
//make sure our recently created database is the active one
mysql_select_db ("carpound");
//delete data
$sql = "DELETE FROM cars WHERE expire = '$today'";
?>
but it still does not delete! any ideas folks -
cheers
mysql_query($sql);
insert this right after the $sql = "... statement ^^
