[SOLVED] Removing old MySQL data with php page

[sonny]

Hi

 

Does anyone know the code for how to delete a old mysql entry older then X

amount of seconds (2400 etc using a php page

 

Sonny

 

I have a table named "visits"

and a unix time stamp field named "time"

[rhodesa]

$before = time() - 2400;
$sql = "DELETE FROM `visits` WHERE `time`< '$before'";

[sonny]

That does not delete anything, even if I set it for 1 second

 

Here is what the database looks like

 

CREATE TABLE `visits` (
  `id` int(11) NOT NULL auto_increment,
  `ip` varchar(20) NOT NULL default '',
  `country` varchar(50) NOT NULL default '',
  `region` varchar(50) NOT NULL default '',
  `city` varchar(50) NOT NULL default '',
  `time` int(11) NOT NULL default '0',
  `vcode` int(11) NOT NULL default '0',
  PRIMARY KEY  (`id`)
);

 

This is what the time field looks like

(unix time)-->1248106995

 

this what my php looks like I tested with

<?php

require "./connect.php";
$before = time() - 5;
$sql = "DELETE FROM `visits` WHERE `time`< '$before'";

?>

 

I would like to delete entry's older then x amount of seconds to test first

 

Thanks for helping

Sonny

[rhodesa]

oh...well...you need to add a mysql_query() to it...i just assumed you knew to do that:

<?php

require "./connect.php";
$before = time() - 5;
$sql = "DELETE FROM `visits` WHERE `time` < '$before'";
mysql_query($sql) or die(mysql_error());

?>

[sonny]

Thank you so much! it worked

 

Would it be hard to covert that to 1 day if ever needed?

or do I just add up the seconds.

 

again thanks

Sonny

[rhodesa]

you can add up the seconds or use the strtotime() function..it's pretty versatile:

$before = strtotime('-1 day');