Database of Web Live Chart Application
Web Live Text Chart Application with PHP and MySQL - Part 3
Forward: In this part of the series, I talk about the database and the tables for the web live chart application.
By: Chrysanthus Date Published: 14 Nov 2012
Introduction
Operation of Application
When the client (user) wants to chart, he clicks a link in a normal web page and a small chart web window opens. He charts with the server operator using this window. At the other end, the server operator has a full web window that is always open (displayed). He carries out his conversation with the client in the window.
It is the client who initiates conversation as follows: The client small window that opens actually displays two documents, one followed by the other. In the first document, the client types in his first name and email address. When he submits this form, two tables called, chartMonitor and discussion are created in a database called, chartdb at the server. The database is already created and is always there. The creation of these tables initiates conversation. In your project, you can give whatever name you want for chartMonitor, discussion and chartdb.
The operator window is always checking for the existence of these tables, through a PHP code at the server. As soon as it sees them it knows that conversation has been initiated. It alerts the operator of this with an ECMAScript alert box. In good browsers this alert box produces a sound. After clicking the OK button of this alert box, the operator types and sends to the client, the first message. From there, conversation continues.
It is the operator who ends the conversation by clicking an End Conversation Button. When he clicks this button, the two tables of the database are erased. The database remains empty.
So, in this project, the client initiates conversation and the operator starts conversation; you will appreciate the meaning of this later.
Code for the Tables
The Create Table code for the two tables is:
create table discussion
(
discussID smallint NOT NULL AUTO_INCREMENT,
Person CHAR(40),
msg varchar(3000),
PRIMARY KEY (discussID ASC)
);
create table chartMonitor
(
tableID smallint default 1,
clientStart char(10),
operatorStart char(10),
clientMsgState char(5),
operatorMsgState char(5),
lastClientID smallint,
lastOperatorID smallint,
clientName varchar(30),
operatorName varchar(30),
clientWriting char(10),
operatorWriting char(10),
clientEmail varchar(40)
);
The discussion table has the messages. The chartMonitor table has data, which changes as the conversation progresses to coordinate the massages.
This table has an auto-increment primary key called, discussID. The name of the second column is Person. When the message for a row is from the operator, the column cell has the value, “operator”. When the message is from the client, the column cell, has the value, “client”.
The third column is msg. When a row has “operator”, the value in this cell is the message from the operator; when a row has “client”, the value in this cell is the message from the client. The conversation consists of messages and they are all in this table.
The chartMonitor Table
The chartMonitor table has only one row; the number of rows does not increase as the conversation continues; it remains at one. The first cell has the ID of the row. When the client initiates the conversation by submitting the first form document, the next cell, clientStart acquires the value “started”. When the operator sends his first message, the cell, operatorStart acquires the value “started”. This same value for the two cells are quickly changed as the conversation continues (see later).
When the operator has not yet read the current message of the client, the cell, clientMsgState acquires the value, “new”. After reading, this value is changed. For a similar reason and action, when the client has not yet read the current message from the operator, the cell, operatorMsgState acquires the value of “new”.
As the conversation continues, the auto-increment ID for the discussion table increases. The last client message ID of the discussion table is kept in the cell, lastClientID. The last operator message ID is kept in the cell, lastOperatorID. These values change with time accordingly.
The cell, clientName has the first name of the client; the cell, operatorName has the actual name of the operator; these values do not change with time.
When the client is typing a new message, the cell, clientWriting acquires the value, “Yes”. After the message is read from the discussion table, the cell acquires the value, “No”. For a similar reason and action, on the part of the operator, the cell operatorWriting would acquire “Yes” and “No”. The cell, clientEmail holds the email address of the client. The email is not used in this project, but a commercial project would use it.
In the project, the root user (DBA) of the MySQL database server, grants privileges for the client and operator as follows:
CREATE USER 'client'@'localhost' IDENTIFIED BY 'clnt';
CREATE USER 'operator'@'localhost' IDENTIFIED BY 'oprtr';
GRANT SHOW DATABASES ON *.* TO 'client'@'localhost';
GRANT CREATE ON chartdb.* TO 'client'@'localhost';
GRANT INSERT ON chartdb.* TO 'client'@'localhost';
GRANT SELECT ON chartdb.* TO 'client'@'localhost';
GRANT UPDATE ON chartdb.* TO 'client'@'localhost';
GRANT SHOW DATABASES ON *.* TO 'operator'@'localhost';
GRANT INSERT ON chartdb.* TO 'operator'@'localhost';
GRANT SELECT ON chartdb.* TO 'operator'@'localhost';
GRANT UPDATE ON chartdb.* TO 'operator'@'localhost';
GRANT DROP ON chartdb.* TO 'operator'@'localhost';
Read through the statements. Remember, if privileges are not granted, a non-root user would not be able to access any table.
That is it for this part of the series. We stop here and continue in the next part.
Chrys