Is there a way to make a PHP script that will delete information from a MYSQL database after a certain amount of time?

---

Is there a way to make a PHP script that will delete information from a MYSQL database after a certain amount of time?

It can only be done on days that end in "y".....

Sumpin' like this?

<?php

//Connect to the Database Server
$con = mysql_connect("myhost","myuser","mypassword");
if (!$con)
  {
      die('Could not connect: ' . mysql_error());
            }

// Select Database to use            
 mysql_select_db("mydatabase", $con);

// $now is the current "unix time"
$now=time();

// Subtract 86400 from $now to get what the unix time was 24 hours ago.
// (there are 3600 "unix ticks" per hour, thus 3600*24=86400 in a day)
$TimeToDelete=$now-86400;

// Do the query, deleting anything older than $TimeToDelete (24 hours)
 mysql_query("DELETE FROM mytable WHERE created < $TimeToDelete");

// Close the connection                              
mysql_close($con);

?>

This assumes that the database "mydatabase" has a table named "mytable" and that table has a column named "created" that contains the unix time stamp for when that row was inserted.

Is there a way to make a PHP script that will delete information from a MYSQL database after a certain amount of time?

It can only be done on days that end in "y".....

Sumpin' like this?

<?php

//Connect to the Database Server
$con = mysql_connect("myhost","myuser","mypassword");
if (!$con)
  {
      die('Could not connect: ' . mysql_error());
            }

// Select Database to use            
 mysql_select_db("mydatabase", $con);

// $now is the current "unix time"
$now=time();

// Subtract 86400 from $now to get what the unix time was 24 hours ago.
// (there are 3600 "unix ticks" per hour, thus 3600*24=86400 in a day)
$TimeToDelete=$now-86400;

// Do the query, deleting anything older than $TimeToDelete (24 hours)
 mysql_query("DELETE FROM mytable WHERE created < $TimeToDelete");

// Close the connection                              
mysql_close($con);

?>

This assumes that the database "mydatabase" has a table named "mytable" and that table has a column named "created" that contains the unix time stamp for when that row was inserted.

Thanks scare crow! I will give it a try tomorrow as I am too tired to do it right now.

---

Is there a way to make a PHP script that will delete information from a MYSQL database after a certain amount of time?

It can only be done on days that end in "y".....

Bummer. Guess that rules out working on sundae. Well, that's a treat anyway.

<ducks and runs for cover>

No need to involve php in the date computations...

This will work if your column is a date, timestamp or datetime type:

DELETE FROM table WHERE created < NOW() - INTERVAL 7 DAY

For an int column with a unix timestamp in it:

DELETE FROM table WHERE created < UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY)

More about dates in mysql: Mysql manual page on date and time functions

---

A member of The Knights Who Say NI!
For rankings, history graphs and more, check out:
My BOINC stats site

No need to involve php in the date computations...

This will work if your column is a date, timestamp or datetime type:

DELETE FROM table WHERE created < NOW() - INTERVAL 7 DAY

For an int column with a unix timestamp in it:

DELETE FROM table WHERE created < UNIX_TIMESTAMP(NOW() - INTERVAL 7 DAY)

More about dates in mysql: Mysql manual page on date and time functions

The idea was to make it work automatically so I would only have to intervene if something went wrong.

---

Well you can still put my queries inside of a php/perl/java/shell script. I just prefer doing things in one place. So instead of spreading the logic between the php script and the SQL I just put it all in the SQL. Either way will work fine.

---

A member of The Knights Who Say NI!
For rankings, history graphs and more, check out:
My BOINC stats site

Well you can still put my queries inside of a php/perl/java/shell script. I just prefer doing things in one place. So instead of spreading the logic between the php script and the SQL I just put it all in the SQL. Either way will work fine.

Additionally, keeping more of the logic in the SQL statement also allows the SQL engine to optimize the query in some cases.

If this is on a *nix machine you may want to look into setting up a cron script if you want the script to run periodically.

---