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.

[JShor]

I use LEFT, INNER and RIGHT JOINs quite often, actually. I've never had much of a use for CONCAT().

[murali1987]

i need image upload in my login page... i need to get some idea ... help me
:p

[murali1987]

i need help

[traq]

i need image upload in my login page... i need to get some idea ... help me
:p

Please don't make off-topic posts: this is Tissy's thread, and the topic is deleting entries from a mysql database.

If you have a different question, start your own thread. For the best response, be specific with what you want, and be sure you include:

1) What you want to accomplish
2) What you have already tried
3) what problems you have encountered
4) all relevant code, and/or a link to the page in question

in addition, make sure you post in the appropriate forum, and research your problem beforehand. Uploading an image, for example, is a very common task and there are many tutorials to be found on the internet. You should not post asking for help until you've made a serious attempt to accomplish your goal on your own, and have run into specific problems that you can't figure out.

[Tissy]

Still on topic, I used the following to weed the database after 1 day (will extend to 7 days, 1 day is easier for testing).



$qry_del = "DELETE FROM temp WHERE timing < UNIX_TIMESTAMP(NOW() - INTERVAL 1 DAY)";


What are people thoughts on resetting the ID.

At the moment the ID is on sequential increament.

However, again over a period of time, this will increase quite exponentially.

I am thinking of at midnight, resetting the ID back to 0, in my mind and for viewing purposes this will keep the database tidy and easier to view.

Next question therefore would be, how would i do that :)

my current PHP looks like 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'];
$humidity = $_GET['h'];

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

// $date = time(); //echo "\n";
// $date = date("Y-m-d H:i:s"); //echo "\n";
// $qry_add = "INSERT INTO temp(timing, temp) VALUES('$date','$temp')";

$qry = "INSERT INTO temp(timing, temp, humidity) VALUES(".time().",'$temp','$humidity')";
$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();
?>

Thank you for your continued knowledge and assistance.

Steve

[ggalan]

if you have an auto increment id you could do this


ALTER TABLE theTableInQuestion AUTO_INCREMENT=1;

[Tissy]

Many thanks, so this command resets the table ID back to 1, how would I then only complete this reset at midnight each day ?

[ggalan]

might have to look into cron jobs

[crobinson42]

CRON jobs are the best bet and very usefull!