MySQL Select records with timestamp after X
I am trying to populate a list of records for the last X seconds of server time.
My current request:
mysql_query("SELECT player.name FROM player, spectate WHERE (player.pid = spectate.player) && spectate.bid=$bid");
This currently works to fetch all records. My first thought was to select the time field. And then use the mktime () function to find the time difference with the current server time, and only print the records with a slight difference.
But I thought it would be more efficient to include WHERE ($ x> $ servertime - spectate.time + 1) along with my other statements.
This doesn't work as I now have what I am doing wrong?
Function with SELECT
function spectateJSON()
{
$servertime = date("Y-m-d H:i:s");
global $json, $bid;
$specResult = mysql_query("SELECT player.name FROM player, spectate WHERE (player.pid = spectate.player) && spectate.bid=$bid && (20 > $servertime - spectate.time + 1)");
$specResultCount=mysql_num_rows($specResult);
$json.= '"spectate":[';
for($i=0; $i<$specResultCount; $i++)
{
$spectatorName=mysql_result($specResult,$i,"name");
$json.='"'.$spectatorName.'"';
if($i+1 != $specResultCount)$json.=",";
}
$json.=']';
}
with UPDATE or INSERT
if(isset($_SESSION['char']))
{
$charId = $_SESSION['char'];
$bid = $_GET['bid'];
$servertime = date("Y-m-d H:i:s");
$specResult = mysql_query("SELECT player FROM spectate WHERE player='$charId'");
$specResultCount=mysql_num_rows($specResult);
if($specResultCount > 0)
{
$Query=("UPDATE spectate SET bid='$bid' time='$servertime' WHERE player='$charId'");
mysql_query($Query);
}
else
{
mysql_query("INSERT INTO spectate (bid, player, time) VALUES ('$bid','$charId','$servertime')");
}
}
Thanks for your help, any other suggestions / criticism is appreciated as well =)
Update:
An example of a table entry.
rate: 169
player: 1
time: 2009-10-20 21:22:54
An example of an entry in the players table:
pid: 1
uid: 1
name: SpikeAi
score: 2000
wins: 0 | lose: 0
tie: 0
source to share
This should capture lines for the last minute:
SELECT
time
FROM
games
WHERE
`time` > DATE_SUB( NOW(), INTERVAL 1 MINUTE )
The type column timestamp
seems to have worked for me. I don't think you necessarily need to generate the time in PHP, as MySQL must have the same time, assuming it is on the same server.
Edit: this one is more compact ...
SELECT * FROM games
WHERE
time > now() - INTERVAL 1 MINUTE
source to share