Deleting old data from mySQL

[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

[murali1987]

i need help

[traq]

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

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!