Simple PHP chat quirk

Question

I've been working on a quick and simple jQuery/PHP chat to put in my website for the visitors to communicate. I've extimated peaks of 200 simultaneous website users (connected users) with at most 10-20 people actually chatting.

Here's the quirk:

As I experienced already twice (thought it seems to be rather an unlikely event more than something happening after you perform something specific) the chat happens to load multiple messages which have already been red and display them.

Trying to keep the chat system as simple as possibile I came up with this code:

---

HTML CODE:

<div class="chat">

    <ul class="chat">

        <li class="chat" >

            <h5 class="chat">Date</h5>
            <h6 class="chat">Time</h6>
            <h4 class="chat">User</h4>
            <br/>
            <q class="chat">Message</q>

        </li>

    </ul>

    <input class="chat" placeholder="write something..."/>

</div>

As you can see I put a placeholder li element for the jQuery to take and use as a snippet to create new li elements with the actual messages and prepend them inside the ul element.

---

jQuery CODE:

Sending messages:

$(document).ready(function(){

    chatSnippet = $('ul.chat').html(); // here chatSnippet is a global variable
    $('ul.chat').html('');

    $('input.chat').change(function(event){// Send your message

    message = $(this).attr('value');

// first thing I perform an asynchronous POST to the receiving php script

    $.post(

        'php/chatRec.php',

        {

            user : currentUser,
            message: message,

        }

    );

// meanwhile I add a new li element to the chat html with the content just submitted


    date.setTime(event.timeStamp);

    hours = ''+date.getHours();

    if(hours.length < 2) hours = '0'+hours;

    minutes = ''+date.getMinutes();

    if(minutes.length < 2) minutes = '0'+minutes;

    day = ''+date.getDate();

    if(day.length < 2) day = '0'+day;

    newChatMessage = chatSnippet.replace('Date', ''+day+' '+months[date.getMonth()]);
    // here months is an array with the months names (in italian)
    newChatMessage = newChatMessage.replace('Time', ''+hours+':'+minutes);

    newChatMessage = newChatMessage.replace('User', connectedUser);

    newChatMessage = newChatMessage.replace('Message', message);

    $mess = $(newChatMessage);

    $mess.hide().prependTo('ul.chat').fadeIn(500);

    $(this).attr('value','');

});

refreshChat(''); // this function retrives new messages from the DB

// Here I perform a void refreshChat call so I'll get all the messages in the DB regardless from the currentUser (at page refresh)

});

Receiving messages:

// This code is placed outside (before) the .ready function

function refreshChat(user){// Receiving messages

$.post(

    'php/chatInv.php',

    {

        user : user,
        token: lastMessage // this variable contains the token of the last red message

    },

    function(data){

        receivedMessages = jQuery.parseJSON(data);

        for(message in receivedMessages){

            message = receivedMessages[message].Message;

            date = receivedMessages[message].Day.split('-');
            time = receivedMessages[message].Time.split(':');

            newChatMessage = chatSnippet.replace('Date', ''+date[2]+' '+months[parseInt(date[1])-1]);

            newChatMessage = newChatMessage.replace('Time', ''+time[0]+':'+time[1]);

            newChatMessage = newChatMessage.replace('User', receivedMessages[message].Sender);

            newChatMessage = newChatMessage.replace('Message', message);

            $mess = $(newChatMessage);

            $mess.hide().prependTo('ul.chat').fadeIn(500);

            lastMessage = receivedMessages[messages].token;

        }

        nextRefresh = setTimeout("refreshChat('"+currentUser+"')",2000);

// When I'm done I set a timeout of 2 secs and then perform another refresh

    }

);

}

---

PHP CODE:

Receive a new message (I think the issue is in here):

    mysql_connect("localhost", "root", "root") or die(mysql_error());
    mysql_select_db("chat") or die(mysql_error());

    $characters = array('0','1','2','3','4','5','6','7','8','9','a','b','c','d','e','f','g','h','i','j','k','l','m','n','o','p','q','r','s','t','u','v','w','x','y','z','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');

    $token = $characters[rand(0,61)].$characters[rand(0,61)].$characters[rand(0,61)].$characters[rand(0,61)].$characters[rand(0,61)];

    $all_Msgs = mysql_query("SELECT * FROM Messages ORDER BY ID");

    $prev_Msg = array('ID' => 1 , 'Sender' => $_POST['user'], 'Message' => $_POST['message'], 'Day' => date("Y-m-d"), 'Time' => date("H:i:s"), 'token' => $token);

    while($Msg = mysql_fetch_array($all_Msgs)){

        $update_success = mysql_query("UPDATE Messages SET Sender='".$prev_Msg['Sender']."', Message='".$prev_Msg['Message']."', Day='".$prev_Msg['Day']."', Time='".$prev_Msg['Time']."', token = '".$prev_Msg['token']."' WHERE ID=".$Msg['ID']);

        $prev_Msg = $Msg;

    }

Basically what I do here is receive the new post message, generate a token and an array element (which is itself an array) containing the new entered datas, done this I perform a seuqence of UPDATE statements on a fixed size SQL table overriding the new datas on the first record and then overriding each record with the previous one (so that the last record will be finally lost).

Sending messages:

    mysql_connect("localhost", "root", "root") or die(mysql_error());
    mysql_select_db("chat") or die(mysql_error());

    $receiver = $_POST['user'];
    $token = $_POST['token'];

    $all_Msgs = mysql_query("SELECT * FROM Messages ORDER BY ID");

    $newMessages = array();

    while($Msg = mysql_fetch_array($all_Msgs)){

        if($Msg['token'] == $token) break;

        if($Msg['Sender'] != $receiver) array_unshift($newMessages,$Msg);

    }

    echo json_encode($newMessages);

So I send the client the JSON encode of an array of all the records in the DB inserted after the last known message and whose author was not the querying client.

---

My suspects:

I came to the conclusion that when the message reception (server side) is being performed there is a time span when each message is taken from the DB, if a refresh is being performed in the meanwhile the message is not found and if that message was the one we were looking for as the last red message then the server will just select all the messages in the table and send them back.

The result is you see a bunch of messages you already red without your messages in between (cuz they were added to the view client side and the server script doesn't send you back your own messages)

Stated that:

• I don't care if the messages aren't exactly in the actual insertion order: let's say A and B are chatting, the actual real messages order is BAB, but A may se the order ABB for his view is immediatly updated at input time (this helps me keep a 'fast-realtime' feel)
• I don't care if some message is lost (like if it falls over the fixed DB table edge before someone can read it)
• At this time I don't care much about actual efficency, speed and optimization
• I know I should probalby handle the message insertion differently adding the new record and then updating the IDs only and delete the last record out. But if possible I'd like to keep this UPDATE-only fashion.

do you think my interpretation of the problem is right? If not: what would then be the cause? / how can I fix that? If yes: how can I fix that easily?

If the actual fix is rather complex: how actually likely to happen would be this quirk in a 10-20 users chat?

Thanks

Answer 1

I noticed this when I worked on a chat code too, the solution is to store the last message ID (set as an Auto Increment field in MySQL) in a session and search the database for messages where the ID is higher than that, rather than use the time() function.

if (!$_SESSION['message_id']]) {
// if there isn't a message_id, select the last seven entries in the message list
    $sql = "SELECT messages.message_id, messages.message, users.username FROM (SELECT * FROM messages, users user.user_id = messages.user_id ORDER BY message_id DESC LIMIT 7) as new_tbl ORDER BY message_id ASC";
} else {
// if there is a message_id, select the messages sent since the last entry
    $sql = sprintf("SELECT messages.message_id, messages.message, users.username FROM messages, users WHERE user.user_id = messages.user_id message_id > '%d'", $_SESSION['message_id']);
}

$data = array();
$query = mysql_query($sql);
while ($row = mysql_fetch_array($query)) {
// build the data array from the mysql result and set the message_id session to the id of the last message
    $data[$i] = array('user' => $row['username'], 'message' => $row['message']);
    $_SESSION['message_id'] = $row['message_id'] > $_SESSION['message_id'] ? $row['message_id'] : $_SESSION['message_id'];
    $i++;
}

Obviously you'd need to escape the session!

If there isn't a message_id session, it loads the last 7 messages from the table (ordered descending, then orders those messages in ascending order). If there is a message_id session, it loads new messages.

In the while loop, it builds a data array (I send it to my script as JSON) and sets the message_id session as the message_id row, with a failsafe check to make sure the message_id session doesn't end up being lowered.

The SQL implies that you have a table of users with the user_id and username, and a table of messages with a user_id, message_id and message.