Problem keeping active users list

[kaiousama]

I've got a small AJAX chat running on my server that I've been working on, and it polls a page (getUsers.php) every 4 seconds to keep them marked as active and checks who is still active in the chat room.  However, at seemingly random times it only lists a single user (the person you're logged in as in the chat).  My code is:

 

header("Expires: Mon, 26 Jul 1997 05:00:00 GMT" ); 
header("Last-Modified: " . gmdate( "D, d M Y H:i:s" ) . "GMT" ); 
header("Cache-Control: no-cache, must-revalidate" ); 
header("Pragma: no-cache" );
header("Content-Type: text/xml; charset=utf-8");

require('database.php');
if (isset($_SESSION['username'])) {
	$sql = "UPDATE users SET last_active = NOW() WHERE username = '" . $player_info['username'] . "'";
	db_query($sql);
}
$sql = "SELECT * FROM users WHERE last_active > NOW() - 15";
$message_query = db_query($sql);
$xml = '<?xml version="1.0" ?><root>';
while($message_array = mysql_fetch_array($message_query, MYSQL_ASSOC)) {
	$xml .= '<user>';
	$xml .= '<username>' . htmlspecialchars($message_array['username']) . '</username>';
	$xml .= '</user>';
}
$xml .= '</root>';
echo $xml;

 

db_query() simply calls a function that does the query and increases a global counter for queries run generating the page.  Can anyone see why this page occasionally only returns a single result of the person that was just updated?

[btherl]

Do things change if you alter the magic "15" in your select query?  I suspect this may be a timing issue.  Try dropping it to 10 or 5 and see if you get more single results.

[kaiousama]

I've tried and didn't notice any real change (obviously 5 is WAY too low though since they only attempt to poll the server every 4 seconds) when I -increased- it to 60.  And ALL users see it, even if 30 people are active, occasionally they'll only see themselves for that one request of active users.

[btherl]

I think it may be due to now() arithmetic.. check out the manual here:

 

"NOW()

 

Returns the current date and time as a value in 'YYYY-MM-DD HH:MM:SS' or YYYYMMDDHHMMSS format, depending on whether the function is used in a string or numeric context. The value is expressed in the current time zone.

 

mysql> SELECT NOW();

        -> '1997-12-15 23:50:26'

mysql> SELECT NOW() + 0;

        -> 19971215235026"

 

That indicates to me that now() - 15 doesn't do what you think it does, as the date is not in "number of seconds" format.  Here's the reference for mysql dates and times:

 

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

[kaiousama]

Aha!  Thank you, it is due to how NOW() calculates with +/-.  Since only 60 seconds are in a minute, once a minute rolls over the difference goes up by an extra 40.