Making an AJAX Web Chat (Part 1) – PHP and MySQL

In this two-part tutorial, we will be creating an AJAX Web Chat using PHP, MySQL and jQuery. In this first part, we will be discussing the PHP & MySQL side, and next week we will continue with the jQuery and CSS front-end. Go to part two.


As usual, the first step is to lay down the HTML markup. Our document is structured as HTML5 for convenience, as this allows us to use the new, shorter (and more memorable) doctype, and skip the type attribute on the script tags.


<!DOCTYPE html>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />

<!-- Loading the jScrollPane CSS, along with the styling of the
     chat in chat.css and the rest of the page in page.css -->

<link rel="stylesheet" type="text/css" href="js/jScrollPane/jScrollPane.css" />
<link rel="stylesheet" type="text/css" href="css/page.css" />
<link rel="stylesheet" type="text/css" href="css/chat.css" />



<div id="chatContainer">

    <div id="chatTopBar" class="rounded"></div>
    <div id="chatLineHolder"></div>

    <div id="chatUsers" class="rounded"></div>
    <div id="chatBottomBar" class="rounded">
        <div class="tip"></div>

        <form id="loginForm" method="post" action="">
            <input id="name" name="name" class="rounded" maxlength="16" />
            <input id="email" name="email" class="rounded" />
            <input type="submit" class="blueButton" value="Login" />

        <form id="submitForm" method="post" action="">
            <input id="chatText" name="chatText" class="rounded" maxlength="255" />
            <input type="submit" class="blueButton" value="Submit" />



<!-- Loading jQuery, the mousewheel plugin and jScrollPane, along with our script.js -->

<script src=""></script>
<script src="js/jScrollPane/jquery.mousewheel.js"></script>
<script src="js/jScrollPane/jScrollPane.min.js"></script>
<script src="js/script.js"></script>

To optimize the load time, the stylesheets are included in the head section, and the JavaScript files in the footer, just before the closing body tag.

We are using the jScrollPane plugin to create the scrollable area with the chats entries. This plugin comes with its own stylesheet, which is the first thing we’ve included into the page.

The markup of the chat consists of four main divs – the top bar, the chat container, the user container and the bottom bar. The latter holds the login and submit forms. The submit form is hidden by default and only shown if the user has successfully logged in the chat system.

An AJAX Web Chat with PHP, MySQL and jQuery

Lastly we include the JavaScript files. Starting with the jQuery library, we add the mousewheel plugin (used by jScrollPane), the jScrollPane plugin itself and our script.js file.

Database Schema

Before we move on with the PHP part, we first have to take a closer look at how the chat data is organized in the MySQL database.

For the purposes of this script we use two tables. In webchat_users we are storing the chat participants. This table has na id, name, gravatar and a last_activity field. The name field is defined as unique, so that no users have duplicate nick names in the chatroom.

Webchat Users Table Structure

Another useful feature of the unique index fields, is that insert queries will fail and the inserted_rows property of the MySQLi object will be set to zero if we attempt to insert a duplicate row. This finds its place in the Chat PHP class you will see in the next step.

The last_activity column holds a timestamp, which is updated every 15 seconds for every user. It is also defined as an index, so it is faster to delete inactive users (having a last_activity column with a greater value than 15, would mean that the user is no longer viewing the chat window).

Webchat Lines Table Structure

The webchat_lines table holds the individual chat entries. Notice that we are storing the author name and gravatar here as well. This duplication is worthwhile as it frees us from using an expensive join when requesting the latest chats – the most frequently accessed feature of the application.


Now that we have the database in place, lets start discussing the PHP scripts that drive the chat.

The first file we are going to take a closer look at, is ajax.php. It handles the AJAX requests sent from the jQuery front end and outputs JSON formatted data.


require "classes/DB.class.php";
require "classes/Chat.class.php";
require "classes/ChatBase.class.php";
require "classes/ChatLine.class.php";
require "classes/ChatUser.class.php";



    // If magic quotes is enabled, strip the extra slashes
    array_walk_recursive($_GET,create_function('&$v,$k','$v = stripslashes($v);'));
    array_walk_recursive($_POST,create_function('&$v,$k','$v = stripslashes($v);'));


    // Connecting to the database

    $response = array();

    // Handling the supported actions:


        case 'login':
            $response = Chat::login($_POST['name'],$_POST['email']);

        case 'checkLogged':
            $response = Chat::checkLogged();

        case 'logout':
            $response = Chat::logout();

        case 'submitChat':
            $response = Chat::submitChat($_POST['chatText']);

        case 'getUsers':
            $response = Chat::getUsers();

        case 'getChats':
            $response = Chat::getChats($_GET['lastID']);

            throw new Exception('Wrong action');

    echo json_encode($response);
catch(Exception $e){
    die(json_encode(array('error' => $e->getMessage())));

For convenience, I’ve used a simple switch statement to define the actions, supported by the script. These include chat submission, login/logout functionality, and actions for requesting a list of chats and online users.

All output is in the form of JSON messages (conveniently handled by jQuery), and errors are raised in the form of exceptions. The switch statement routes all requests to the appropriate static method of the Chat class, which we will discuss later in this section.


class DB {
    private static $instance;
    private $MySQLi;

    private function __construct(array $dbOptions){

        $this->MySQLi = @ new mysqli(	$dbOptions['db_host'],
                                        $dbOptions['db_name'] );

        if (mysqli_connect_errno()) {
            throw new Exception('Database error.');


    public static function init(array $dbOptions){
        if(self::$instance instanceof self){
            return false;

        self::$instance = new self($dbOptions);

    public static function getMySQLiObject(){
        return self::$instance->MySQLi;

    public static function query($q){
        return self::$instance->MySQLi->query($q);

    public static function esc($str){
        return self::$instance->MySQLi->real_escape_string(htmlspecialchars($str));

The DB class is our database manager. The constructor is private, which means that no objects can be created from the outside, and the initialization is only possible from the init() static method. It takes an array with MySQL login details, and creates an instance of the class, held in the self::$instance static variable. This way we can be sure that only one connection to the database can exists in the same time.

The rest of the classes take advantage of the static query() method to communicate with the database.


/* This is the base class, used by both ChatLine and ChatUser */

class ChatBase{

    // This constructor is used by all the chat classes:

    public function __construct(array $options){

        foreach($options as $k=>$v){
                $this->$k = $v;

This is a simple base class. It’s main purpose is to define the constructor, which takes an array with parameters, and saves only the ones that are defined in the class.


/* Chat line is used for the chat entries */

class ChatLine extends ChatBase{

    protected $text = '', $author = '', $gravatar = '';

    public function save(){
            INSERT INTO webchat_lines (author, gravatar, text)
            VALUES (

        // Returns the MySQLi object of the DB class

        return DB::getMySQLiObject();

Here is the ChatLine class. It extends ChatBase, so you can easily create an object of this class by providing an array with a text, author, and gravatar elements. The gravatar property contains a md5 hash of the person’s email address. This is required so we can fetch the user’s gravatar from

This class also defines a save method, which the object to our database. As it returns the MySQLi object, contained in the DB class, you can check whether the save was successful by checking the affected_rowsproperty (we will come back to this in the Chat class).


class ChatUser extends ChatBase{

    protected $name = '', $gravatar = '';

    public function save(){

            INSERT INTO webchat_users (name, gravatar)
            VALUES (

        return DB::getMySQLiObject();

    public function update(){
            INSERT INTO webchat_users (name, gravatar)
            VALUES (
            ) ON DUPLICATE KEY UPDATE last_activity = NOW()");

The same is also valid here. We have the name and gravatar properties (notice the protected access modifier – this means that they will be accessible in the ChatBase class, so we can set them in the constructor).

The difference is that we also have an update() method, which updates the last_activity timestamp to the current time. This shows that this person keeps a chat window open and is displayed as online in the users section.

Chat.class.php – Part 1

/* The Chat class exploses public static methods, used by ajax.php */

class Chat{

    public static function login($name,$email){
        if(!$name || !$email){
            throw new Exception('Fill in all the required fields.');

            throw new Exception('Your email is invalid.');

        // Preparing the gravatar hash:
        $gravatar = md5(strtolower(trim($email)));

        $user = new ChatUser(array(
            'name'        => $name,
            'gravatar'    => $gravatar

        // The save method returns a MySQLi object
        if($user->save()->affected_rows != 1){
            throw new Exception('This nick is in use.');

        $_SESSION['user']    = array(
            'name'        => $name,
            'gravatar'    => $gravatar

        return array(
            'status'    => 1,
            'name'        => $name,
            'gravatar'    => Chat::gravatarFromHash($gravatar)

    public static function checkLogged(){
        $response = array('logged' => false);

            $response['logged'] = true;
            $response['loggedAs'] = array(
                'name'        => $_SESSION['user']['name'],
                'gravatar'    => Chat::gravatarFromHash($_SESSION['user']['gravatar'])

        return $response;

    public static function logout(){
        DB::query("DELETE FROM webchat_users WHERE name = '".DB::esc($_SESSION['user']['name'])."'");

        $_SESSION = array();

        return array('status' => 1);

This is where all the work gets done. Remember the switch statement in ajax.php above? It maps the supported actions with the corresponding methods from this class. Each of these methods returns an array, as it is later converted to a JSON object with the internal json_encode() function (this happens at the bottom of ajax.php).

When the user logs in, their name and gravatar get saved as elements of the $_SESSION array, and become available on consecutive requests. We will be using this to validate that the user is allowed to add chats later on.

You can also see how we are preparing the gravatar hash. This is done according to their best practices guide and ensures that if the person has configured a Gravatar, it will be properly displayed.

Chat.class.php – Part 2

    public static function submitChat($chatText){
            throw new Exception('You are not logged in');

            throw new Exception('You haven\' entered a chat message.');

        $chat = new ChatLine(array(
            'author'    => $_SESSION['user']['name'],
            'gravatar'    => $_SESSION['user']['gravatar'],
            'text'        => $chatText

        // The save method returns a MySQLi object
        $insertID = $chat->save()->insert_id;

        return array(
            'status'    => 1,
            'insertID'    => $insertID

    public static function getUsers(){
            $user = new ChatUser(array('name' => $_SESSION['user']['name']));

        // Deleting chats older than 5 minutes and users inactive for 30 seconds

        DB::query("DELETE FROM webchat_lines WHERE ts < SUBTIME(NOW(),'0:5:0')");
        DB::query("DELETE FROM webchat_users WHERE last_activity < SUBTIME(NOW(),'0:0:30')");

        $result = DB::query('SELECT * FROM webchat_users ORDER BY name ASC LIMIT 18');

        $users = array();
        while($user = $result->fetch_object()){
            $user->gravatar = Chat::gravatarFromHash($user->gravatar,30);
            $users[] = $user;

        return array(
            'users' => $users,
            'total' => DB::query('SELECT COUNT(*) as cnt FROM webchat_users')->fetch_object()->cnt

    public static function getChats($lastID){
        $lastID = (int)$lastID;

        $result = DB::query('SELECT * FROM webchat_lines WHERE id > '.$lastID.' ORDER BY id ASC');

        $chats = array();
        while($chat = $result->fetch_object()){

            // Returning the GMT (UTC) time of the chat creation:

            $chat->time = array(
                'hours'        => gmdate('H',strtotime($chat->ts)),
                'minutes'    => gmdate('i',strtotime($chat->ts))

            $chat->gravatar = Chat::gravatarFromHash($chat->gravatar);

            $chats[] = $chat;

        return array('chats' => $chats);

    public static function gravatarFromHash($hash, $size=23){
        return ''.$hash.'?size='.$size.'&default='.

As you will see in the next part of this tutorial, jQuery sends a getUsers() request every 15 seconds. We are using this to delete chats older than 5 minutes and inactive users from the database. We could potentially delete those records in getChats, but that is requested once every second and the extra processing time could severely impact the performance of our app.

Another thing worth noting, is that, in the getChats() method, we are using the gmdate function to output a GMT time. In the frontend, we use the hour and minute values to feed the JavaScript date object, and as a result all the times are displayed in the user’s local time.