[SOLVED] Delete row if time is more than

[UQ13A]

Hi,

how do i delete a row from my table if it is more than 24 hours old

 

my code to insert the row into the MYSQL database is

<?php
include("db.php"); // Connect to DB

$browser = $_SERVER['HTTP_USER_AGENT'];
$IP = $_SERVER['REMOTE_ADDR'];
$page = $_SERVER['PHP_SELF'];

$now = date("Y-m-d H:i:s", strtotime('+6 hours')); // My server is 6 hours behind my local timezone

mysql_query("INSERT INTO `[logs]`(`time`,`IP`,`browser`,`page`) values('$now','$IP','$browser','$page')") or die(mysql_error());

?>

 

So in other words...

if the row has been in the table for more than 24 hours i would like to delete it itself.

Can anybody help me out with a basic script

 

thanks

[JAY6390]

you just need to check if now subtract date field is greater than or equal to 86400 (1 day in seconds)

 

You will need to set up a cron job script to do this automatically

[UQ13A]

you just need to check if now subtract date field is greater than or equal to 86400 (1 day in seconds)

 

You will need to set up a cron job script to do this automatically

 

 

thanks but how would i go around doin this?

Im a total newbie at PHP and MYSQL

 

is the script like this?

?<php
$logs = mysql_query("SELECT * FROM `[logs]`") or die(mysql_error());
$row = mysql_fetch_array($logs);

$dt = "86400";
$time = $row['time'];

if($time - $dt >= 86400) {
echo "Success";
} else {
echo "Error";
}
?>

 

I have tried it but i cant get it to echo "Success"

[UQ13A]

Please ignore my above comment 

 

here is my code

<?php

$logs = mysql_query("SELECT * FROM `[logs]` WHERE id = '1'") or die(mysql_error());
$row = mysql_fetch_array($logs);

$now = date("Y-m-d H:i:s", strtotime('+6 hours'));

$dt = "86400";
$time = $row['time'];

$diff = $now - $time;

if($diff >= $dt) {
print "Delete Now";
} else {
echo "Dont delete";
}

?>

[JAY6390]

Is the field for the date and time an actual datetime field or just a varchar field?

[UQ13A]

Is the field for the date and time an actual datetime field or just a varchar field?

 

i have used a timestamp

 

so when i insert using $now it inserts like this 2009-07-12 15:13:06

 

have i done it wrong?

[JAY6390]

Yes I saw that, I mean when you created the table, what type of field is the one you are putting that in exactly?

[UQ13A]

Yes I saw that, I mean when you created the table, what type of field is the one you are putting that in exactly?

 

i am putting it in the field called time

[JAY6390]

lol yes I know. I mean what field TYPE is the TIME field (such as int, varchar, float, decimal)? can you run code in phpmyadmin?

if so can you run the query

DESCRIBE logs

and paste the result

[UQ13A]

lol yes I know. I mean what field TYPE is the TIME field (such as int, varchar, float, decimal)? can you run code in phpmyadmin?

if so can you run the query

DESCRIBE logs

and paste the result

 

i couldnt seem to do it with [logs] so i copied the structure and data to a table called logs (from operations)

 

Field  Type  Null  Key  Default  Extra 

      id int(11) NO PRI NULL auto_increment

      page varchar(250) NO  none 

      browser varchar(250) NO  NULL 

      IP varchar(250) NO  NULL 

      time timestamp NO  CURRENT_TIMESTAMP 

      owner smallint(1) NO  1 

      ref_web varchar(255) NO  NULL

 

 

[UQ13A]

I will check this tomarrow as i need some sleep

 

thanks for the help tho

[UQ13A]

Im back now

 

still having trouble with this

[UQ13A]

I found this code while searching this website

 

<?php

mysql_query("DELETE FROM `logs` WHERE time + 86400 >= UNIX_TIMESTAMP()") or die(mysql_error());
echo "Deleted";

?>

 

But it deletes all rows from my table

 

[UQ13A]

damn i need this to work its been almost 2 days  :'(

 

and JAY6390 i posted my table stuff

[Psycho]

Your operator is wrong. It would delete records which are less than 24 hours old. Plus, you need to convert the time field to seconds as well.

 

DELETE FROM `logs`
WHERE UNIX_TIMESTAMP(`time`) + 86400 < UNIX_TIMESTAMP()

[UQ13A]

Your operator is wrong. It would delete records which are less than 24 hours old. Plus, you need to convert the time field to seconds as well.

 

DELETE FROM `logs`
WHERE UNIX_TIMESTAMP(`time`) + 86400 < UNIX_TIMESTAMP()

 

 

Thanks for that but i can seem to get it to work in php it works in phpmyadmin

 

<?php
include("db.php");

$sql = 'DELETE FROM `logs` WHERE UNIX_TIMESTAMP(`time`) + 86400 < UNIX_TIMESTAMP()' or die(mysql_error());

if(($sql))
{

echo '<p>Logs Successfully Deleted!</p>';

} else {

echo '<p>Error</p>';
}

?>

 

and it doesnt give any errors

[UQ13A]

Ah ha i found the error

 

fixed it

i changed the following line from

<?php
$sql = 'DELETE FROM `logs` WHERE UNIX_TIMESTAMP(`time`) + 86400 < UNIX_TIMESTAMP()' or die(mysql_error());
?>

to

<?php
$sql = mysql_query("DELETE FROM `logs` WHERE UNIX_TIMESTAMP(`time`) + 86400 < UNIX_TIMESTAMP()") or die(mysql_error());
?>