Deleting old data from mySQL

[Tissy]

Hi,

haven't posted here in a while, but just started using some PHP for a mySQL database.

I have a microprocessor that uploads data to a mySQL via php.

At the moment the database adds an entry for every piece of data sent, so basically the id is incremented each time.

Due to the amount of data being processed, this database is going to get pretty big rather quickly.

I have posted the current php script below.

How would I go about, and is it possible, to store 7 days worth of data and then start overwriting the data on day 8 again for example, therefore I only effectively have 1 weeks worth of data.

I am very new to PHP & mySQL, so go easy :-)

I would also like to store time as GMT (UK time).

Hope all this makes sense and any help or guidance is appriciated !

Steve

<?php
// connect to MySQL
mysql_connect('server','user','password') or die("Can't connect that way!");
@mysql_select_db('arduino') or die("Unable to select a database called 'Arduino'");
if(ISSET($_GET['t']) && (is_numeric($_GET['t'])) && $_SERVER['REMOTE_ADDR']=='192.168.1.142'){
  // message from the Arduino
  $temp = $_GET['t'];
  $date = date("Y-m-d H:i:s"); //echo "\n";
   
  $qry = "INSERT INTO temp(timing, temp) VALUES('$date','$temp')";
  mysql_query($qry);
  mysql_close();
  exit('200');
}
mysql_close();
?>

[JShor]

Well, unfortunately MySQL data does not have an expiring cache. You would have to set one of your fields to store a timestamp. From there, whenever you want to put/post/get your data, check the timestamp to make sure it's not more than 7 days old. This will simulate data being no more than 7 days old.

For example, let's say you have a column named 'timestamp' which stored UNIX time, and you want to retrieve records younger than 7 days. For this, you'll need to store time using time() instead of date(). You can always convert it later.

$seven_days = strtotime("-7 day");

$query = mysql_query("SELECT * FROM temp WHERE timing > $seven_days") or die(mysql_error()); 

And you would modify your current code to this:

<?php
// connect to MySQL
mysql_connect('server','user','password') or die("Can't connect that way!");
@mysql_select_db('arduino') or die("Unable to select a database called 'Arduino'");
if(ISSET($_GET['t']) && (is_numeric($_GET['t'])) && $_SERVER['REMOTE_ADDR']=='192.168.1.142'){
  // message from the Arduino
  $temp = $_GET['t'];
  $date = time(); //echo "\n";
   
  $qry = "INSERT INTO temp(timing, temp) VALUES('$date','$temp')";
  mysql_query($qry);
  mysql_close();
  exit('200');
}
mysql_close();
?>

[Tissy]

Hi and many thanks for taking the time to reply.

With the code you have posted, I'm guessing the data base will still continue to populate past 7 days.

What i need is that the database ONLY contains 7 days worth of data to ensure it doesn't grow too large.

The database is basically recording temperature.

At the moment, I only call the last recorded temperature. However, as my experience with PHP and mySQL grows, I may decide to call the last 24hrs worth of data for example, so having historical data could be useful. But do not need it past the 7 days.

Is this functionality possible with my current code ?

Thanks again.

[JShor]

It's possible to run a CRON job that deletes data every day. Cron jobs only exist in Linux Apache, but there are other solutions if you're not using that.

[traq]

if you use a cron job, i would suggest still using a timestamp, and deleting only the data that is already a week old. that way, even though you might have two weeks of data at some point, you would never be caught without the week of data you needed.

you might also export the old entries to a text file before deleting them, as a permanent backup.

[Tissy]

Thanks for the replies, its appriciated.

Ideally I would like to include this weeding process into the current PHP as this PHP is called evry 10 minutes to update the database with a new entry.

My database colums basically look like this:

ID Timing Temp
32 1316183454 29

I presume the 'Timing' is currently stored as a unix timestamp.

Is it possible to add a command to the current php script that completes the weeding for me automatically?

I've come up with this so far, but don't want to wait for a day to see if it will work :-)

<?php
// connect to MySQL
mysql_connect('server','user','password') or die("Can't connect that way!");
@mysql_select_db('arduino') or die("Unable to select a database called 'Arduino'");
if(ISSET($_GET['t']) && (is_numeric($_GET['t'])) && $_SERVER['REMOTE_ADDR']=='192.168.1.142'){
  // message from the Arduino
  $temp = $_GET['t'];

  $qry = "INSERT INTO temp(timing, temp) VALUES(".time().",'$temp')";
  $qry_del = "DELETE FROM temp WHERE timing < UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)";
  mysql_query($qry);
  mysql_query($qry_del);
//  OPTIMIZE TABLE temp
  mysql_close();
  exit('200');
}
mysql_close();
?>

I have set the weed for 1 day for testing at the moment, but i would also like it to weed as per traq recommendation which makes sense.

I would suggest still using a timestamp, and deleting only the data that is already a week old. that way, even though you might have two weeks of data at some point, you would never be caught without the week of data you needed.

Suggestions welcomed to automate this process using just the one php script as above.

Many thanks.

[traq]

I'm always mistrustful of mysql timestamps, but that looks like it should work fine. Personally, I would calculate the time in php beforehand:

$one_day_ago = time() + 60*60*24; 
// now - 60sec x 60min x 24hrs = 1 day ago 

[Tissy]

Thank you for taking the time to reply, are you suggesting i use the following instead, replacing the 'INTERVAL 1 DAY' with the predefined variable ?

$qry_del = "DELETE FROM temp WHERE timing < UNIX_TIMESTAMP($one_day_ago)"; 

Thanks again,

Steve

[JShor]

I had no idea that UNIX_TIMESTAMP() and INTERVAL 1 DAY existed in SQL syntax. Very interesting, and useful.

[traq]

there is a lot of cool, underused stuff in SQL. JOINs, FULLTEXT, and CONCAT(), for example.