CODEWITHSUNDEEP
phpsqlmysql
Bolt_Head
Bolt_Head

Reputation: 1463

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 =)

Update:

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

Upvotes: 2

Views: 5025

Answers (1)

meder omuraliev
meder omuraliev

Reputation: 186562

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

Upvotes: 5

Related Questions