No timestamps working >:(

[AcidReignX]

I've tried just about every php/mysql combination out there in order to make this work, but even when it seems that it should work, it doesn't. It must be comparing strings or something, because I've tested the numbers, and the numbers themselves would have worked properly.

What I'm trying to do is create a "Who's Online" box for a forum. I've already created a timestamp record in my user table, and they're updated with NOW() whenever somebody navigates anywhere on the site.

I've tried to run this with many functions, and the most recently failed one is this:

$query = "SELECT recorda, recordb FROM usertable WHERE timestamp >= " . (date("YmdHi") - 10) . date("s");
$result = mysql_query($query);

...

Now... the numbers seem to come out right if it would just CALCULATE THEM properly. Whenever the fifth to last digit is changed, it says everybody is online. I can't seem to find a combination that works. I've used NOW() - ###, but that does the same thing.

I'm reeeaaallllly getting frustrated with this thing. Could somebody please help me with this?

 

[AcidReignX]

Of course.. just when I post I find something that could work.

...

Well, either that or work for a while and then screw up, just like all the other ones. At any rate, could somebody verify that this code would work?

$query = "SELECT recorda, recordb FROM usertable WHERE timestamp + 1000 >= NOW()";
$result = mysql_query($query);

...

So far it seems to be working okay. I've tested it with multiple numbers (as opposed to +1000), and nothing I've done has screwed it up thus far... which is a good sign, but I've been fooled by these before.

Should that work (and continue to)?

 

[ericbrunson]

In the first post, you were comparing timestamp to a string (incorrect), in the second you are comparing it to a datetime (correct).

Glad you figured it out.

 

[AcidReignX]

Hmm... I dunno. Every once in a while (possibly near the end or beginning of an hour), it will still calculate everything wrong again for some reason...

Is there some equation to prevent it?

 

[TonyGroves]

I can't see any reason why that query would ever fail.

However, there are a couple of improvements you could make:

1. "timestamp" is a MySQL keyword; it would be a good idea to change it to something else if possible.

2. You would be better saying "WHERE timestamp >= NOW() - 1000". That form compares a column against a constant, ensuring that an index on `timestamp` would be used, if available.