MySQL Select entries with timestamp after X time

I am attempting to populate a list of records within the last X seconds of the server time.

My current query is..

mysql_query("SELECT player.name FROM player, spectate WHERE (player.pid = spectate.player) && spectate.bid=$bid");

This works currently to retrieve all entries. My first thought was to select the time field as well. And then use the mktime() function to find the time difference with the current server time, and only print records with a small difference.

But I figured that it would be more effective to include WHERE ($x > $servertime - spectate.time + 1) along with my other statements.

It doesn't work as I have it now what am I 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.=']';
}

function 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 / critique is welcome as well =)

Spectate table entry example.

bid: 169

player: 1

time: 2009-10-20 21:22:54

Player table entry example:

pid: 1

uid: 1

name: SpikeAi

score: 2000

wins: 0

lose: 0

tie: 0

This should grab rows within the last minute:

SELECT

time

FROM

games

WHERE

`time` > DATE_SUB( NOW(), INTERVAL 1 MINUTE )

On a timestamp column type seemed to work for me. I don't think you necessarily need to generate the time in PHP as it should be the same time in MySQL assuming it's on the same server.

Edit: This one is more compact...

SELECT * FROM games

WHERE

time > now() - INTERVAL 1 MINUTE