Page 1 of 2 12 LastLast
Results 1 to 10 of 19

Thread: Deleting old data from mySQL

  1. #1
    Join Date
    Mar 2010
    Location
    Essex, UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default Deleting old data from mySQL

    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 Code:
    <?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();
    ?>
    Last edited by Tissy; 09-15-2011 at 10:07 AM.

  2. #2
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    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.

    PHP Code:
    $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 Code:
    <?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();
    ?>
    - Josh

  3. #3
    Join Date
    Mar 2010
    Location
    Essex, UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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.

  4. #4
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

    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.
    - Josh

  5. #5
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    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.

  6. #6
    Join Date
    Mar 2010
    Location
    Essex, UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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 Code:
    <?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.

  7. #7
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

    I'm always mistrustful of mysql timestamps, but that looks like it should work fine. Personally, I would calculate the time in php beforehand:
    PHP Code:
    $one_day_ago time() + 60*60*24
    // now - 60sec x 60min x 24hrs = 1 day ago 

  8. #8
    Join Date
    Mar 2010
    Location
    Essex, UK
    Posts
    11
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Default

    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 ?

    PHP Code:
    $qry_del "DELETE FROM temp WHERE timing < UNIX_TIMESTAMP($one_day_ago)"
    Thanks again,

    Steve

  9. #9
    Join Date
    Mar 2007
    Location
    New York, NY
    Posts
    557
    Thanks
    8
    Thanked 66 Times in 66 Posts

    Default

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

  10. #10
    Join Date
    Apr 2008
    Location
    So.Cal
    Posts
    3,643
    Thanks
    63
    Thanked 516 Times in 502 Posts
    Blog Entries
    5

    Default

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

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •