Building an AJAX-Based Chat: Interacting With a Database

As I said previously, before I start coding the pertinent PHP files for accessing the database, first allow me to list the corresponding MySQL wrapping class, which I plan to use within the PHP scripts. Here is how it looks:

class MySQL{
    var $conId; // connection identifier
    var $host; // MySQL host
    var $user; // MySQL username
    var $password; // MySQL password
    var $database; // MySQL database
    var $result; // MySQL result set
    // constructor
    function MySQL($options=array()){
        // validate incoming parameters
        if(count($options)<1){
            trigger_error('No connection
parameters were provided');
            exit();
        }
        foreach($options as $parameter=>$value){
            if(!$parameter||!$value){
                trigger_error('Invalid
connection parameter');
                exit();
            }
            $this->{$parameter}=$value;
        }
        // connect to MySQL
        $this->connectDB();
    }
    // connect to MYSQL server and select
database
    function connectDB(){
        if(!$this->conId=mysql_connect($this-
>host,$this->user,$this->password)){
            trigger_error('Error connecting to
the server '.mysql_error());
            exit();
        }
        if(!mysql_select_db($this-
>database,$this->conId)){
 trigger_error('Error selecting database '.mysql_error());
 exit();
        }
    }
    // perform query
    function query($query){
        if(!$this->result=mysql_query
($query,$this->conId)){
 trigger_error('Error performing query
'.$query.' '.mysql_error());
 exit();
        }
    }
    // fetch row
    function fetchRow(){
        return mysql_fetch_array($this-
>result,MYSQL_ASSOC);
    }
    // count rows
    function countRows(){
        if(!$rows=mysql_num_rows($this-
>result)){
 trigger_error('Error counting rows');
 exit();
        }
        return $rows;
    }
    // count affected rows
    function countAffectedRows(){
        if(!$rows=mysql_affected_rows($this-
>conId)){
 trigger_error('Error counting affected rows');
 exit();
        }
        return $rows;
    }
    // get ID from last inserted row
    function getInsertID(){
        if(!$id=mysql_insert_id($this->conId)){
 trigger_error('Error getting ID');
 exit();
        }
        return $id;
    }
    // seek row
    function seekRow($row=0){
        if(!mysql_data_seek($this-
>result,$row)){
 trigger_error('Error seeking data');
 exit();
        }
    }
}

Although I’ll use only the “query()” and “getInsertID()” methods of the above MySQL class, having an idea of how the whole class looks makes it easier to understand the logic behind the corresponding PHP files. In simple terms, the first file “sendchatdata.php” will connect to the database, then obtain the message submitted by the user, along with his/her nickname, and finally insert the data into a single table. In order to keep things simple, the structure of the database table will be defined by an ID field (the primary key), and a string “message” field, useful for storing chat messages. As you can see, the schema is extremely understandable.

Once a message is added to the database table, the script will fetch the last twenty messages, and send them out directly to the client. Certainly, I’ve chosen arbitrarily the number of messages to be retrieved, but this parameter can be easily changed to meet particular needs.

Now that you know how chat messages will be fetched from the database table, below is the definition for the “sendchatdata.php” file:

// include class file
require_once 'mysql_class.php';
// connect to MySQL
$db=&new MySQL(array('host'=>'host','user'=>'user',
'password'=>'password','database'=>'chat'));
// get user & message
$user=$_POST['user'];
$message=$_POST['message'];
// insert new message into database table
$db->query("INSERT INTO messages SET user='$user',message='$message'");
// get ID from last inserted message
$id=$db->getInsertID();
// delete messages when ID > 1000
if($id>1000){
    $db->query("DELETE FROM messages WHERE id <
($id-10)");
}
// retrieve last 20 messages
$db->query("SELECT user,message FROM messages
WHERE id <=$id ORDER BY id DESC LIMIT 20");
// send messages to the client
while($row=$db->fetchRow()){
 echo '<'.$row['user'].'>'.$row['message'].'|';
}

As shown above, the file uses the MySQL wrapping class for connecting to the database, inserting a new message together with the chosen nickname, and lastly fetching the last twenty messages. The final “while” loop is responsible for echoing the data, so it can be grasped within the JavaScript application by the “responseText” property belonging to the proper XMLHttpRequest object. Please note how basic formatting is applied to the data, and how each table row is separated by a pipe character (“|”), so it can be processed as an array structure.

The last thing to be noted is the addition of the following checking line:

if($id>1000){
    $db->query("DELETE FROM messages WHERE id <
($id-10)");
}

Essentially, the task of this line is to clean up the messages table by deleting most of them, when the table contains more than 1,000 messages. Again, this parameter is optional and configurable, so you can change it, in order to suit your specific requirements.

Having defined the first PHP file, which inserts and fetches chat messages from the database table, it’s time to move forward, in order to write the second server script, responsible for reading user messages. So, let’s jump straight into the next section and see how this is done.