Delete record if older than 5 minutes?

[ERuiz]

Can someone help me in writing a query command, to delete a record, if the last_update field on a record is older than 5 minutes?

 

My table has a last_update field, which holds a value as this for example: 2007-04-08 01:33:12

 

I need a query that when I access a php file, if a record is older than 5 minutes, it will delete it.

 

Thanks for any help.

 

ERuiz

[PC Nerd]

um, theres anothe thread on page 2, 3, or 4 on this, look into it

 

 

but the essence is

 

$now = strtotime("now");

$then = strtotime("5 minutes ago");

 

if ($pag4e['time']  >= $then) {

  echo now."<br>";

  DO STUFF

  echo "Record was older than".$then;

}

 

 

goo dluck

[ERuiz]

Hello PC Nerd,

 

I didn't understand your example. I don't see how it would apply to my problem. Let me try to explain again.

 

I just need a query which would be something similar like this:

 

$query = DELETE FROM liveacars WHERE last_update "is older than 5 minutes from the current time"

 

And the format for last_update, is like this: 2007-04-08 01:33:12

[PC Nerd]

ok, look intot the time functions

 

 

eg $now = time();

 

if($then > strtotime("5 minutes ago")) {delete}

 

becauase the time is like an integer, its simply saying if its older than 5 minutes ago, then delete

 

 

 

[ERuiz]

I am still unable to get this working.  :-\

 

Look at this code:

 

function DateAdd($interval, $number, $date) {

    $date_time_array = getdate($date);
    $hours = $date_time_array['hours'];
    $minutes = $date_time_array['minutes'];
    $seconds = $date_time_array['seconds'];
    $month = $date_time_array['mon'];
    $day = $date_time_array['mday'];
    $year = $date_time_array['year'];

    switch ($interval) {

        case 'y':
        case 'yyyy':
            $year+=$number;
            break;
        case 'q':
            $year+=($number*3);
            break;
        case 'm':
            $month+=$number;
            break;
        case 'd':
        case 'w':
            $day+=$number;
            break;
        case 'ww':
            $day+=($number*7);
            break;
        case 'h':
            $hours+=$number;
            break;
        case 'n':
            $minutes+=$number;
            break;
        case 's':
            $seconds+=$number;
            break;
    }

    $timestamp= mktime($hours,$minutes,$seconds,$month,$day,$year);
    return $timestamp;
}


    $query = "DELETE FROM liveacars WHERE last_update <= ".date("YmdHis", DateAdd("n", -5, time())).";";
    $result = mysql_query($query) or die("DELETE SQL query failed");

 

The code above, does what I need but it is not working on my database. In order for this code to work, how does my last_update field be formatted? Right now, entries inside last_update, appear as this: 2007-04-08 16:40:12

 

And I notice that the code above, references something as "YmdHis". Could this be the reason why it's not working?

[Barand]

DELETE FROM liveacars WHERE last_update < NOW() - INTERVAL 300 SECOND

[ERuiz]

Hi Barand!

 

I tried it:

 

$query = "DELETE FROM liveacars WHERE last_update < NOW() - INTERVAL 300 SECOND";
$result = mysql_query($query) or die("DELETE SQL query failed");

 

When I ran the script, it still did not delete the record. WOuld you like to see a dump schema of the table? Maybe I have something setup wrong.

[ERuiz]

Here is a schema of the table, just in case you guys spot something wrong:

 

CREATE TABLE `liveacars` (
  `id` int(7) NOT NULL auto_increment,
  `pilot_id` varchar(255) default NULL,
  `pilot_name` varchar(255) default NULL,
  `position` text,
  `position_google` varchar(255) default NULL,
  `ac_type` varchar(255) default NULL,
  `origin_icao` varchar(255) default NULL,
  `origin_name` varchar(255) default NULL,
  `destination_icao` varchar(255) default NULL,
  `destination_name` varchar(255) default NULL,
  `flight_number` varchar(255) default NULL,
  `altitude` int(6) default NULL,
  `heading` int(3) default NULL,
  `vs` int(6) default NULL,
  `true_heading` varchar(255) default NULL,
  `ias` int(4) default NULL,
  `mach` varchar(255) default NULL,
  `tas` int(4) default NULL,
  `gs` int(4) default NULL,
  `zfw` int(6) default NULL,
  `fuel_used` int(6) default NULL,
  `fuel_available` int(6) default NULL,
  `e1n1` varchar(255) default NULL,
  `e1n2` varchar(255) default NULL,
  `e2n1` varchar(255) default NULL,
  `e2n2` varchar(255) default NULL,
  `e1ff` varchar(255) default NULL,
  `e2ff` varchar(255) default NULL,
  `autopilot` varchar(255) default NULL,
  `flaps` int(2) default NULL,
  `transponder` int(4) default NULL,
  `landing_lights` varchar(255) default NULL,
  `strobe_lights` varchar(255) default NULL,
  `nav_lights` varchar(255) default NULL,
  `sim_rate` varchar(255) default NULL,
  `pause_mode` varchar(255) default NULL,
  `landing_gear` varchar(255) default NULL,
  `spoilers` varchar(255) default NULL,
  `current_wx` varchar(255) default NULL,
  `status` varchar(255) default NULL,
  `destination_wx` varchar(255) default NULL,
  `flown_route` longtext,
  `dist_planned` int(6) default NULL,
  `dist_flown` int(6) default NULL,
  `ete` varchar(255) default NULL,
  `eta` varchar(255) default NULL,
  `last_update` timestamp NULL default NULL,
  PRIMARY KEY  (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 AUTO_INCREMENT=1 ;

-- 
-- Dumping data for table `liveacars`
-- 

INSERT INTO `liveacars` VALUES (31, 'VAA1', 'Efrain Ruiz', 'N18° 2.52''  W63° 6.62''', '18.0419, -63.1103', 'B767-300ER', 'KMIA', 'MIAMI INTERNATIONAL AIRPORT', 'TJSJ', 'SAN JUAN LUIS MUNOZ MARIN INTL', 'AAL265', 26, 338, 0, '325', 0, '.00', 0, 0, 241669, 4167, 14919, '24', '65', '24', '65', '3859', '3859', 'OFF', 0, 1200, 'ON', 'ON', 'ON', '1', 'OFF', 'DOWN', 'DOWN', '41930Z 00000KT 60SM SKC 15/5 A2992', '3', 'Unable to connect to WX server or ICAO not found', 'new GLatLng(18.0417,-63.1134)', 166, 0, '', '', '2007-04-08 16:40:12');

 

Thanks for any help, guys! I really do appreciate it.

[Barand]

I created a table from your dump then ran the delete query. Record was deleted ???

[ERuiz]

??? Now you got me thinking! lol Damn, what the heck could it be... Let me try again then...

[ERuiz]

:'( I keep trying that query and it is not working for me. The record just isn't deleted. I even ran the query from phpMyAdmin and it still said no records affected.