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.
Next: Reading messages from the server: defining the “getchatdata.php” file >>
More XML Articles
More By Alejandro Gervasio
|
| · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | · | | | | |
|
/ 32 
