[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 6262
  • Last Modified:

How to delete data after some time from mysql table automatically after a inetrval of time. eg: 60 mins. Using php.

How to delete data after some time from mysql table automatically after a inetrval of time. eg: 60 mins. Using php. I basically want that data from the column to get expired after some time or become Null or Zero. Any ideas how to do it. I know that  TIMESTAMP DEFAULT CURRENT_TIMESTAMP field  can be used to track how old the data is. But I don't know how to use it. Can anyone give a an code example?
0
Paul_Perry
Asked:
Paul_Perry
  • 2
3 Solutions
 
DrDamnitCommented:
Use a cron job to run a script like the one below:
<?php
	$link = mysql_connect($server,$user,$password);
	$db = mysql_select_db('yourdb');
	$sql = "DELETE FROM `your_table` WHERE TIMESTAMPDIFF(MINUTES,CURRENT_TIME(),`saved_timestamp`) > 3600";
	$r = mysql_query($sql);
?>

Open in new window

0
 
DrDamnitCommented:
You would need to run it once / minute...
0
 
KalpanCommented:
@Paul_Perry

Please refer the attached code to setup with ur server which will manipulate the query from your table and check the data/record with current timestamp of the table...

you can also set the crontab -e to run the php file every hour or as you like

$bash: crontab -e

60 0 * * * wget -qb http://your_domina/repo/testfile.php > /dev/null

Please let me know if you need more to assist..

Hope this will be helpful...

Thanks, Kalpan
<?

ini_get('max_execution_time'); 


                $host = '127.0.0.1';
                $user = 'user';
                $pass = 'pass';
                $db = 'myDB';

                $link = mysql_connect($host,$user,$pass);
                if(!$link)
                        die('Could not connect to mysql dadtabase'.mysql_error());

                $condb = mysql_select_db($db,$link);
                if(!$condb)
                        die('Could not select the HitsDB Database:'.mysql_error());

//$currentDate = new Date('Y-m-d ');

$interval=60; //minutes
set_time_limit(0);


$now=time();
$timetoexpire = $interval*60-(time()-$now));


$sqlQuery = 'SELECT * from hitslog where currtime < '.$timetoexpire;

$result = mysql_query($sqlQuery, $link);
$norows = mysql_num_rows($result);

if($norows > 0){
   $data = mysql_fetch_array($result);

   foreach($data as $expdata){
	$hitslogId = $expdatap['Id']; // id of current record

	// update the record now to set the expflag to 0 or soemthing
	$sqlQuery = 'UPDATE hitslog set expflag=0 where hitslogId='.$hitslogId;
	mysql_query($sqlQuery);

	// or delete the record
	$sqlQuery = 'DELETE FROM hitslog where hitslogId='.$hitslogId;
	mysql_query($sqlQuery);

   }
   
}else
  // TODO: redirect the page if there is no reocrd found...
   

}

Open in new window

0
 
Ray PaseurCommented:
You can use a DATETIME field in one of the columns.  Then you can use a CRON - type job to identify the records older than 60 minutes.  

But there may be something you want to think about here... Instead of deleting them outright, a smarter design pattern might be to mark them obsolete, thus making them unavailable for the general public, but still available to specialized queries.  This is the sort of thing that adds value to an application.  For example, you could prune the table and store the older records in a history table.  That design pattern would give you the high performance of a smaller "active" table and the complete records of the historic table.

Just a thought, ~Ray
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now