#1 2006-07-31 02:21:57
Newbie database delete records problem
Hi
I am a complete newbie. I have a highscore database for a flash game. The table name is x4tables and each record has the fields id, pname, score and tdate( a timestamp).
I have it all working fine but I wish to delete all scores that are over 7 days old, and the line of code that I have created to delete the old scores does not work. I have spent hours fiddling but I have obviously not got it right.
Any help would be much appreciated.
The code is
Code::
<?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");
//below is the problem line
mysql_query("delete * FROM x4tables WHERE ((time()-strtotime(["tdate"]))>$keeptime) ");
$rs=mysql_select_db("scores",$conn)or die("err:db");
$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();
?>I,m sure that time() and strtotime should be in blue to work but the "" marks stop that?
Offline
#2 2006-08-01 03:58:27
Re: Newbie database delete records problem
You cannot perform php function on mysql data (at least I think you can't, am I right on this one?). So you need to compare your tdate to your converted $keeptime variable.
mysql_query("delete FROM x4tables WHERE tdate>$keeptime");
Offline
#4 2006-08-03 02:49:21
Re: Newbie database delete records problem
HI,
is that tdate you want to convert?
your query would like :
mysql_query("delete FROM x4tables WHERE UNIX_TIMESTAMP(tdate)>$keeptime");
assuming inserted values are like :
'0000-00-00'
or
'0000-00-00 00:00:00'
Offline
