A JQuery Chat: A Sample chat_aux.php

In a prevous article demonstrating the development of a JQuery chat, I noted that several server-side aspects depended on the specific implementation. I thought, however, I would provide a sample chat_aux.php for the demonstration to be complete. Of course, in real-life scenarios, things would certainly be a lot more complicated than this.

So let’s begin by creating a simple database structure in a MySQL database. As a bare minimum, we will need a users table to record when a user was last ‘seen’ and a chat table to record the conversation.

CREATE TABLE `chat_user` (
  `username` varchar(250) NOT NULL,
  `last_chat` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
);

CREATE TABLE `chat` (
  `mark` int(11) NOT NULL AUTO_INCREMENT,
  `msg_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  `username` varchar(250) NOT NULL,
  `message` text NOT NULL,
  PRIMARY KEY (`mark`)
);

Now, for the php part, a sample chat_aux.php could be:

<?php
// Database Connection
$dbconn=new mysqli('host','dbuser','dbpass','db');
$dbconn->query('set names utf8');

// Determine the username for the current session
$t=date("Y-m-d H:i:s");
session_start();
if(!isset($_SESSION['username'])) 
    {
	$_SESSION['username']='user_'.rand();
	$dbconn->query("insert into chat_user (username,last_chat) values ('".$_SESSION['username']."','$t')");
	}
else
	$dbconn->query("update chat_user set last_chat='$t' where username='".$_SESSION['username']."'");

// Utility function to fetch all rows from a parametrized stmt
function get_dataset($stmt)
	{
	$r=array();
	if($stmt->execute()&&($d=$stmt->get_result()))
		while($row=$d->fetch_assoc()) $r[]=$row;
	return $r;
	}

// get_username(): returns the username of the current user
function get_username() { return $_SESSION['username']; }

// get_conversation(): returns the last hour's messages from $mark onwards
function get_conversation($mark)
	{
	global $dbconn;
	$limit=date("Y-m-d H:i:s",strtotime('-1 hour'));
	$sql="select mark,msg_time,username,message from chat where mark>? and msg_time>?";
	$stmt=$dbconn->prepare($sql);
	$stmt->bind_param('ss',$mark,$limit);
	return get_dataset($stmt);
	}

// get_active_users(): returns all active users (last_chat not earlier than 15secs ago)
function get_active_users()
	{
	global $dbconn;
	$not_seen=date("Y-m-d H:i:s",strtotime('-15 sec'));
	$sql="select username from chat_user where last_chat>?";
	$stmt=$dbconn->prepare($sql);
	$stmt->bind_param('s',$not_seen);
	return get_dataset($stmt);
	}
	
// say(): records a user's message to the database
function say($user,$msg)
	{
	global $dbconn;
	global $t;
	$m=stripslashes(htmlspecialchars($msg));
	$sql="insert into chat(msg_time,username,message) values (?,?,?)";
	$stmt=$dbconn->prepare($sql);
	$stmt->bind_param('sss',$t,$user,$m);
	return get_dataset($stmt);
	}

The connection to the database is pretty straightforward. Next, we initialize the session, give the user a new random username and record his presence in the chat_user table. There’s also a convenient get_dataset() function, meant to execute a prepared mysqli statement and fetch all rows returned.

The get_username() function is easy: just return the session’s username, as initialized by the code in the beginning. All other functions communicate with the database using prepared statements and get_dataset() to return the data retrieved. More specifically, get_conversation() will return all the messages with a mark greater than $mark, but within the last hour. get_active_users() assumes a user is active if there’s a record for them within the last 15 seconds in the chat_user table. Finally, say() will record the user’s message to the chat table, after stripping out any included html, for obvious reasons 😉