AS3+PHP+MySQL : home-made online chat not always reactive

Question

Hello, I coded some kind of online chat system with a Flash (AS3) client that sends and retrieves data from a PHP script that communicates with a MySQL database.

It works well, except for the fact that occasionally, my client doesn't get one of the new chat lines even though they are correctly registered in the database.

Here is the basic logic behind it : every user has an entry in the database, and each of those entries has a lastChatCheck BigInt(16) variable. When a user types and sends a chat line, the client sends it to a PHP script, where it is stored in the database, along with the date it was stored at (it is also a BigInt(16) ). The flash client checks every 250 milliseconds through a getter PHP script for every chat line where (line's date>user's lastChatCheck), then sets the user's lastChatCheck to the last retrieved line's date. (Please tell me if this is confusing or poorly explained.)

I am positive that the code I wrote to send a new line is correct, as everything I send gets correctly stored in the database.

The (simplified) PHP code for getting the chat lines is like so :

$result=mysql_query('SELECT lastChatCheck FROM players WHERE id='.$id);
while ($row=mysql_fetch_array($result))
{
$lastChatCheck=$row['lastChatCheck'];
}

$output='';

$chatDate=0;

$result=mysql_query('SELECT * FROM chat WHERE date>'.$lastChatCheck);
while ($row=mysql_fetch_array($result))
{
$chatSayer=$row['sayer'];
$chatText=$row['text'];
$chatDate=$row['date'];
$result2=mysql_query('SELECT name FROM players WHERE id='.$chatSayer);
while ($row2=mysql_fetch_array($result2))
{
$output.=$row2['name'].' : '.$row['text'].'<br>';
//(in reality, $output is formatted as an XML object)
}

}


if ($chatDate!=0) mysql_query('UPDATE players SET lastChatCheck='.$chatDate.' WHERE id='.$id);

Is there any logical reason why this would skip some of the recent chat lines ? Is there any way I can fix this ?

Answer 1

If I check for chats at the same time as one gets posted, meaning that the bigint is the same for the check time and the posting, then it's possible that a chat message could get added for the "current time" after you have fetched the chats for that time. In other words, you could have this sequence:

TIME 0: Client A fetches chats. Sets lastChatCheck = 0.

TIME 1: Client A fetches chats > 0. Sets lastChatCheck = 1. Client B posts chat, time 1.

TIME 2: Client A fetches chats > 1. Does not see client B post.

Instead of using a BigInt, why not use TIMESTAMP?

Also, in your PHP code, consider doing a JOIN so you don't have to do a separate MYSQL call for every chat line.