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

Posted on 2010-01-12
Medium Priority
Last Modified: 2013-12-12
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?
Question by:Paul_Perry
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
LVL 32

Accepted Solution

DrDamnit earned 168 total points
ID: 26300984
Use a cron job to run a script like the one below:
	$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

LVL 32

Expert Comment

ID: 26300986
You would need to run it once / minute...
LVL 14

Assisted Solution

Kalpan earned 168 total points
ID: 26301042

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


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

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

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

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

$interval=60; //minutes

$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;

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

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


Open in new window

LVL 111

Assisted Solution

by:Ray Paseur
Ray Paseur earned 164 total points
ID: 26303198
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

Featured Post

Tutorial: Introduction to Managing a Linux Server

In this tutorial on systemd, we will explore:
-OS/Distro Adoption
-chkconfig and Other Legacy Commands
-Summary and Key Commands

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This article will show how Aten was able to supply easy management and control for Artear's video walls and wide range display configurations of their newsroom.
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

765 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question