Home arrow XML arrow Page 2 - Building an AJAX-Based Chat: Interacting With a Database
XML

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.

Author Info:
By: Alejandro Gervasio
Rating: 4 stars4 stars4 stars4 stars4 stars / 52
November 21, 2005
TABLE OF CONTENTS:
  1. · Building an AJAX-Based Chat: Interacting With a Database
  2. · Adding messages to the database: looking at the “sendchatdata.php” file
  3. · Reading messages from the server: defining the “getchatdata.php” file
  4. · Putting the files together: the complete chat application at a glance

print this article
SEARCH DEVARTICLES

Building an AJAX-Based Chat: Interacting With a Database - Adding messages to the database: looking at the “sendchatdata.php” file
(Page 2 of 4 )

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.


blog comments powered by Disqus
XML ARTICLES

- Open XML Finally Supported by MS Office
- XML Features Added to Two Systems
- Using Regions with XSL Formatting Objects
- Using XSL Formatting Objects
- More Schematron Features
- Schematron Patterns and Validation
- Using Schematron
- Datatypes and More in RELAX NG
- Providing Options in RELAX NG
- An Introduction to RELAX NG
- Path, Predicates, and XQuery
- Using Predicates with XQuery
- Navigating Input Documents Using Paths
- XML Basics
- Introduction to XPath

Watch our Tech Videos 
Dev Articles Forums 
 RSS  Articles
 RSS  Forums
 RSS  All Feeds
Write For Us 
Weekly Newsletter
 
Developer Updates  
Free Website Content 
Contact Us 
Site Map 
Privacy Policy 
Support 

Developer Shed Affiliates

 




© 2003-2017 by Developer Shed. All rights reserved. DS Cluster - Follow our Sitemap
Popular Web Development Topics
All Web Development Tutorials