Building an AJAX-Based Chat: Interacting With a Database
In this third and final part of a tutorial that explains how to build an AJAX-based chat application, Alejandro Gervasio covers creating the PHP files that directly interact with a MySQL database, adding and fetching chat messages. He also makes some suggestions for expanding the functions of the application.
Building an AJAX-Based Chat: Interacting With a Database - Reading messages from the server: defining the “getchatdata.php” file (Page 3 of 4 )
As I mentioned earlier, the “getchatdata.php” file performs a smaller task. It only fetches the last twenty messages from the database in descending order and sends the data back to the client to be processed. By sticking to the same approach I tackled for adding messages to the database, I’ll use the MySQL abstraction class for running SELECT queries. The definition for this file is the following:
// include class file require_once 'mysql_class.php'; // connect to MySQL $db=&new MySQL(array('host'=>'host','user'=>'user', 'password'=>'password','database'=>'chat')); // retrieve last 20 messages $db->query("SELECT user,message FROM messages ORDER BY id DESC LIMIT 20"); // send messages to the client while($row=$db->fetchRow()){ echo '<'.$row['user'].'>'.$row ['message'].'|'; }
If you’re patient enough to compare the above file with the one I wrote for inserting chat messages, you’ll realize that they’re closely similar. Undoubtedly, I could have merged both files into one, but I decided to keep them as separate pieces of code, so they have clearly delimited tasks and the overall application is easier to read and understand.
In this case, the above file connects to MySQL, then runs the SELECT query and fetches the first twenty rows. Finally, they’re formatted and transmitted back to the client, for processing by the JavaScript functions that I wrote in my previous articles.
At this point, the files parsed on the server have been properly defined, which demonstrates how messages are pushed into and pulled out from the database. Now, the reason for working with two mutually independent requester objects should be clear. Given that, let’s complete the server-side code by defining the SQL statements that create a sample “messages” table in MySQL. It is as follows:
CREATE TABLE messages ( id INT(4) UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY, user CHAR(256) NOT NULL, message CHAR(256) NOT NULL )
That’s it. The above statements build up the “messages” table, useful for storing user nicknames and chat messages. Now, having defined the two complementary PHP files that access the chat database, as well as the simple SQL code for creating the pertinent database table, the next step involved in developing the AJAX-based chat will be listing all the files that integrate the whole application. So, are you ready to digest lengthy code? Fine, keep on reading.