newbie wants to delete old records

johnm1958

I want to delete records that are more than 7 days old($keeptime). I am new to this and can't figure out the correct code. The table is called 'x4tables' and the field 'tdate' is a timestamp. I have used:

$query=" delete * FROM x4tables WHERE ((time()-strtotime(["tdate"]))>$keeptime) ";

This does not work. Could anyone help please?

<?php
$keeptime=(7*24*60*60);
$database="x4tables";
$user="root";
$conn=mysql_connect("localhost","root","")or die("err:conn");

$self=$_SERVER['PHP_SELF'];
$pname=$_REQUEST['pname'];
$score=$_REQUEST['score'];

$rs=mysql_select_db("scores",$conn)or die("err:db");

$query=" delete * FROM x4tables WHERE ((time()-strtotime(["tdate"]))>$keeptime) ";

$sql="insert into x4tables (pname,score)values(\"$pname\",$score)";
$rs=mysql_query($sql,$conn);


$sql="select pname,score from x4tables order by score limit 10";
$rs= mysql_query($sql,$conn);

$num= mysql_num_rows($rs);

// FLASH DATA CREATED HERE
for($i=0;$i<10;$i++)

		{echo ("pname" . $i . "=");
		echo (mysql_result ($rs,$i,"pname"));
		$mins=0;
		$secs=mysql_result($rs,$i,"score");
		$mins=floor($secs/60);$secs=$secs%60;
		echo("&mins".$i."=");
		echo($mins);
		echo ("&secs" . $i . "=");
		echo($secs);
		echo ("&");
}
mysql_close();

?>

Thanks

johnm1958

i'm beginning to wonder if the problem is in the conversion of the timestamp to the UNIX time?

Any help would be much appreciated. Apologies for such a simple problem, but have only a beginner.

johnm1958

i'm beginning to wonder if the problem is in the conversion of the timestamp to the UNIX time?

Any help would be much appreciated. Apologies for such a simple problem, but am only a beginner.

etully

I can't see the value of the field "tdate" in the table called x4tables but let's assume that you're already writing the date in Unix time.

If you are doing that, then to delete anything that's OVER a week old would be like this:

$one_week_ago = time() - (7 24 60 * 60);
$query = "delete from x4tables where tdate < $one_week_ago";
$result= mysql_query($query,$conn);

Notice that I said, "delete from", not "delete * from". I think that was one main mistake.
Also notice that I used a less than symbol, not a greater than. You want any integers that are LOWER.

I'm not sure what you were getting at with this: WHERE ((time()-strtotime(["tdate"]))
Maybe it's to allow for the fact that you're not storing the date in Unix time in your table? If so, that's fine. If you're not storing the date in Unix time (lots of people don't), then there is simply a different way of writing the query to delete anything that's over a week old... and I don't have that syntax at my fingertips right now... but it's much more like english than the Unix time that I'm used to. Even so, you still want to use "delete from", not "delete * from" and also, you will use < not >. That should be a good start.

johnm1958

the database puts the timestamp on in the form 2006-12-01 etc. However the time() function returns a UNIX number, so I need to convert to match. However I notice that because I have "" in the line, the functions time() and strtotime are not shown in blue and presumably don't work.
How do i get around this please?

johnm1958

Hi
I've tried the code bleow to convert the time to UNIX but no luck.

$one_week_ago = time() - (7 24 60 * 60);
$tdate=strtotime("tdate");
$query = "delete from x4tables where $tdate < $one_week_ago";
$result= mysql_query($query,$conn);