auto delete after time...

chadt

I have a database that holds dates for when personnel should be trained (For corporate use). A section within the corporation can advertise to other sections the number of training slots available. However, I want this 'advertisement' to auto delete itself after a certain amount of time (30 days for example).

So basically, how would I have a row auto delete from mySQL after a certain amount of time has passed. Like having the row 'expire'.

Thanks!

chadT

feigenblatt

This is an wild guess cause I don't use MySQL (PostgreSQL here).

I would say that you should run a cron job daily that deletes the records older than 30 days. Let's say at 3AM.

I do DB maintenance via cron jobs.
Some use python.
Others pass SQL queries to psql.

cheald

Store a record creation time in every record. I'd suggest the unix timestamp. Then, run a cron job that runs a script like this one:

$days = 30;
$time = time() - (60 60 24 * $DAYS);
$sql = "DELETE FROM table WHERE time < '$time'";
$result = mysql_query($sql);

If you don't have cron access, just write this script, and have it run as a "piggyback" off of some other commonly accessed page. That is, say you're accessing your database. Just run that code every time that script runs, substuting the table and time variables for the necessary ones.

Anon

$days = 30;
$time = time() - (60 60 24 * $DAYS);
$sql = "DELETE FROM table WHERE time < $time";
$result = mysql_query($sql);

This wouldn't work for me until I removed the single quotes around the $time variable. My table has a column called time which is of type TIMESTAMP:

i.e. alter table articles add column time timestamp not null;

mochatiffin

what does time<$time means?

dagon

mochatiffin this thread is 9 years old, you should start a new thread with your question

bradgrafelman

mochatiffin;10971509 wrote:
what does time<$time means?

It's an inequality that returns true if the 'time' column is less than the value of $time.