Hi
I have been struggling for weeks now with simple problem concerning timestamps. I am inputting data ('pname and 'score') into a table ('x4tables') and adding a timestamp ( fieldname is 'tdate').
The timestamp is in the form
2006-07-22 17:51:29
When a user interrogates the table, I wish to delete all records that are more than 7 days old ($keeptime) and display 10 of the remaining records.
I am completely lost. It seems to me that things would be a lot easier if I could add the timestamp to the database as a UNIXtime number but I don't know how to do it. Otherwise I somehow need to convert the timestamp to a UNIXtime number in my php routine and then compare it to the current UNIXtime but I can't get that to work either.
Unfortunately I am new to php and sql and may well be using incorrect brackets, syntax etc and may well not be understanding some very basic principles. I have obviouly made some sort of error as on the line that contains 'delete' the functions time() and strtotime do not appear in blue and so presumably are not recognised as functions but only as text.
If you can help I would be incredibly grateful.
My code is below. Incidentally do any members on this forum do freelance paid work, I could do with some help.
My code is below.
<?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
John
