Delete from database after set time

[gavin.sibley]

I am trying to find a way of deleting entries from a database after a certain time, and also when there is another field in the database set to 0.

 

i have got so far with the code (will delete when other field is at 0) but nothing happens when i try to add in the time. my code im trying to use is below

 

mysql_query("DELETE FROM temp_users WHERE book_stage='0' and WHERE time() > [creation+60]");

 

creation being the name of the field in the database that holds the unix time stamp of when the entry was created.

 

i need the entry to be deleted one hour after it was created if the book_stage hasnt been changed to 1 during this hour.

 

many thanks,

gavin

[DavidAM]

1. Change your second WHERE to AND

2. Use the mySql functions for date/time manipulation

 

DELETE FROM temp_users WHERE book_stage='0' and AND UNIX_TIMESTAMP() > creation + 60

 

This assumes, of course, that the creation column is an integer datatype.

[Thisnamewilldo]

Here is how I would do it personally..

 

$thetime = $_SERVER['REQUEST_TIME'];

$maxtime = $thetime - 60;

mysql_query("DELETE FROM temp_users WHERE book_stage='0' AND creation < $maxtime");

[gavin.sibley]

Here is how I would do it personally..

 

$thetime = $_SERVER['REQUEST_TIME'];

$maxtime = $thetime - 60;

mysql_query("DELETE FROM temp_users WHERE book_stage='0' AND creation < $maxtime");

 

Thanks for this it seems to be working great, just one question. Is the - 60; part the length of time after the entry is inserted into the database that it will wait before being deleted? if so is this measured in seconds? so there for, if i changed it to say 120, would that mean it would wait 2 mins before deleteing the entry?

 

sorry for the basic questions but i have never used PHP before and am trying to fix parts of a website that i have had dumped on me.

 

thanks,

gavin

[Thisnamewilldo]

Here is how I would do it personally..

 

$thetime = $_SERVER['REQUEST_TIME'];

$maxtime = $thetime - 60;

mysql_query("DELETE FROM temp_users WHERE book_stage='0' AND creation < $maxtime");

 

Thanks for this it seems to be working great, just one question. Is the - 60; part the length of time after the entry is inserted into the database that it will wait before being deleted? if so is this measured in seconds? so there for, if i changed it to say 120, would that mean it would wait 2 mins before deleteing the entry?

 

sorry for the basic questions but i have never used PHP before and am trying to fix parts of a website that i have had dumped on me.

 

thanks,

gavin

 

No problem, glad it is working. The -60 part says after 60 seconds the entries will be deleted. It basically just gets the current time then takes away 60 seconds and finds any entries less than that time. It is measured in seconds, yes, so if you changed it to 120 it would mean 2 minutes before deletion.

 

- Vince.