Ajax Driven Chat Limitations

query this...query that

[stargateanubis14]

I am attempting to use the AJAX Driven web Chat,as found here [dynamicajax.com]. I have gotten the chat to work properly in the way it was made to work. What i am attempting to do is limit the number of messages shown on the screen, so that only the last 10 messages are shown. Because when new users access the page, it causes the massive amounts of messages to be loaded from all time, which is very impractical.
I thought a solution would be in editing the query data in getChat.php as follows:
**********************

else {
 $last = (isset($_GET['last']) && $_GET['last'] != '') ? $_GET['last'] : 0;
 $chatqry=mysql_query("SELECT * from message");
 $chatqry=mysql_num_rows($chatqry);
 $chatlim=($chatqry-10);
 $sql = "SELECT message_id, user_name, message, date_format(post_time, '%h:%i') as post_time" .
 " FROM message WHERE chat_id = " . db_input($_GET['chat']) . " AND message_id > " . $last . " LIMIT $chatlim, 10";
 $message_query = db_query($sql);
//Loop through each message and create an XML message node for each.
 while($message_array = db_fetch_array($message_query)) {
 $xml .= '<message id="' . $message_array['message_id'] . '">';
 $xml .= '<user>' . htmlspecialchars($message_array['user_name']) . '</user>';
 $xml .= '<text>' . htmlspecialchars($message_array['message']) . '</text>';
 $xml .= '<time>' . $message_array['post_time'] . '</time>';
 $xml .= '</message>';
 }
}

**********************

The above code gets the number of messages in the database, and then sets the starting point 10 from the last message, thus only returning the last 10 messages in the query. This does return the proper query, but the chat fails to update, unless the user manually updates the entire page.

I am not all that familiar with ajax or the xml counterparts being used in this chat. Any assistance in this matter would be greatly appreciated

[Fotiman]

Note, the solution to your problem is actually a change to the PHP code, so it probably doesn't belong in the JavaScript forum. That said...

I took a quick look. The application is sending an AJAX request every 2 seconds to get the latest chat messages, passing the message_id value of the 'last' message it received (the SELECT query uses this value to get only the records with a higher message_id). The first time a user hits the page, that value will be 0 (in other words, it will get all records).

First, the number of records that you are counting is not going to be the same as the actual number returned by the subsequent query. You are doing this:

SELECT * from message

But your query should include the same WHERE clauses as the one that is going to return results, so it should be:

SELECT message_id 
FROM message 
WHERE chat_id = " . db_input($_GET['chat']) . " AND message_id > " . $last

Note, I am only selecting the message_id column (more efficient than selecting *).

Lets say my table has 100 messages in it. The first time the page is hit, $last = 0, so this will result in a query like:

SELECT message_id FROM message WHERE chat_id = 1 AND message_id > 0

$chatqry will equal 100 because it will grab all of the records in my table. Next, you set $chatlim to be 100 - 10 = 90. So your LIMIT statement looks like this:

LIMIT 90,10

In other words, only return 10 records starting at row 90. The first time the page loads, this works exactly as you would expect. However, once the page is loaded, it continues to call this function every 2 seconds, passing in the message_id of the 'last' message it received. So you end up with:

SELECT message_id FROM message WHERE chat_id = 1 AND message_id > 100

This will return only the new records. So far so good. But now when you try to limit the results, here's what happens: Lets say there is one new message that hasn't been displayed yet. $chatqry = 1, so $chatlim = 1 - 10 = -9. So your LIMIT looks like this:

LIMIT -9, 10

Which results in a MySQL error. A quick look at the AJAX response confirms this:

<font color="#000000"><b>1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '-9,10' at line 1<br><br>SELECT message_id, user_name, message, date_format(post_time, '%h:%i') as post_time FROM message WHERE chat_id = 1 AND message_id > 100 LIMIT -9,10<br><br><small><font color="#ff0000">[STOP]</font></small><br><br></b></font>

Once 10 new records have arrived, you would then see those new messages because you'd have a limit like this:

LIMIT 0, 10

So, you need to do 2 things.
1. You need to make sure LIMIT will never contain a negative x value:
2. You need to differentiate between a first time load and the differential updates. For the differential updates, you don't want to limit the number of rows fetched, you want everything newer than the last message.

Here are the changes that I made to get this working:

[pre]
} else {
  $last = (isset($_GET['last']) && $_GET['last'] != '') ? $_GET['last'] : 0;
  $limit = ''; // No limit unless this is a first time load
  if ($last == 0 ¦¦ $last == '0') { // First time load, so limit the results
    $chatqry = mysql_query("SELECT message_id FROM message WHERE chat_id = " . 
        db_input($_GET['chat']) . " AND message_id > " . $last);
    $chatqry = mysql_num_rows($chatqry);
    $limitDuration = 10; // The max number of rows to fetch on first load
    if ($chatqry > $limitDuration) {
      $limit = "LIMIT " . ($chatqry - $limitDuration) . ', ' . $limitDuration;
    }
  }
  $sql = "SELECT message_id, user_name, message, date_format(post_time, '%h:%i') as post_time" . 
      " FROM message WHERE chat_id = " . db_input($_GET['chat']) . " AND message_id > " . $last . 
      ' ' . $limit;
  $message_query = db_query($sql);

[/pre]

Give that a try and let us know if it works.

[Fotiman]

Note, make sure you replace ¦¦ with real pipe characters (the forum software replaces pipe characters with ¦¦).