Chart Operator PHP Interface Code for MySQL
Web Live Text Chart Application with PHP and MySQL - Part 5
Forward: In this part of the series, I discuss the chart operator PHP interface code for MySQL.
By: Chrysanthus Date Published: 14 Nov 2012
Introduction
There are 4 PHP interface files for the operator. Remember, there are two tables in the database: chartMonitor and discussion. chartMonitor has the data that monitors the messages in the discussion table. The tables are created by the first document (Form document) of the client’s window.
The File to Read Message from Client
This file (program) is called by the operator web page, every 10 seconds. The name of the file whose content is a program is, operatorRead.php. The program receives as data from the operator web page, the firstname of the client (this can also be read from the chartMonitor table – you can make that change in your commercial program if you are ready to do more and consistent coding). Remember that the chartMonitor table has just one row, with a cell for clientStart, a cell for clientMsgState, a cell for lastClientID, a cell for clientName, a cell for clientWriting, and a cell for clientEmail. The clientStart cell holds the value, “started” when the client fills the form with his name and email address and sends to the server. For simplicity, this cell is always read. The clientMsgState cell holds the value “new” if the message from the client is current (not been read) or “old” if the last message sent by the client had been read by this program (file) and sent to the operator. The lastClientID cell holds the last (maximum) ID in the discussion table of the last message sent by the client. The clientName cell holds the actual name of the client; for simplicity, this cell is always read. Whenever the client is typing (writing), the cell, clientWriting holds the value, “Yes”, otherwise it holds the value, “No”. The clientEmail cell holds the email address of the client. All these data are read every 10 seconds by this program (file).
//select all the row items of the only row of chartMonitor
$chartMonitorSelectStr = "select * from chartMonitor";
$monitorSelectResult = mysql_query($chartMonitorSelectStr,$link);
if (!$monitorSelectResult)
{
echo NULL;
}
else
{
$row = mysql_fetch_assoc($monitorSelectResult);
$clientStart = $row['clientStart'];
$clientName = $row['clientName'];
$clientEmail = $row['clientEmail']; //read but not needed for this pedagogic project
$clientWriting = $row['clientWriting'];
//update cells of the chartMonitor
$updateStr = "update chartMonitor SET clientStart="already" WHERE tableID=1";
mysql_query($updateStr,$link);
//read new message from client if available
$selectClientMsgStateStr = "select clientMsgState from chartMonitor WHERE tableID=1";
$stateResult = mysql_query($selectClientMsgStateStr,$link);
$row = mysql_fetch_assoc($stateResult);
$clientMsgState = $row['clientMsgState'];
if ($clientMsgState == "new")
{
//select last operator discussID
$selectIDStr = "select lastClientID from chartMonitor WHERE tableID=1";
$IDResult = mysql_query($selectIDStr,$link);
$row = mysql_fetch_assoc($IDResult);
$ID = $row['lastClientID'];
//now select message from discussion table
$selectMsgStr = "select msg from discussion WHERE discussID=$ID";
$msgResult = mysql_query($selectMsgStr,$link);
$row = mysql_fetch_assoc($msgResult);
$msg = $row['msg'];
}
//update chartMonitor, form return string and send to operator
if (($clientStart == "started")||($clientMsgState == "new")||($clientWriting=="Yes"))
{
$updateStr = "update chartMonitor SET clientMsgState = "old" WHERE tableID=1";
mysql_query($updateStr,$link);
//update clientWriting cell after successful reading
if ($clientMsgState == "new")
{
$updateWStr = "update chartMonitor SET clientWriting="No" WHERE tableID=1";
mysql_query($updateWStr,$link);
}
$returnStr = "clientName=" . $clientName . "&" . "msg=" . "$msg" . "&" .
"clientWriting=" . $clientWriting . "&" . "clientStart=" . $clientStart;
echo $returnStr;
}
}
At the bottom of the program, the chartmonitor table is updated on condition that the value of clientStart is “started” or the value of clientMsgState is “new” or the value of clientWriting is “Yes”. If the value of clientWriting is “Yes”, then it is set to “No”; however the value of yes is sent to the operator. The value of “No” would be sent in the next reading.
Any program called by Ajax may or may not return a text string. This one called by Ajax in the operator web page, returns a text string (i.e. sends a string to Ajax in the operator web page). The text string returned is equivalent to:
“clientName=value&msg=value&clientWriting=value&clientStart=value”
For simplicity, the values for clientName and clientStart are always returned. The value for clientName is the actual name of the client. The value for msg is the recent message. If there is no recent message depending on the value of clientMsgState (new) the chartMonitor table is not updated for clientWriting. The value of clientWriting is “Yes” or “No”. The value of clientStart is “started” or “already”. The web page ECMAScipt uses these values accordingly (see previous part of the series). Note the return statement and the condition for which data is returned, in the code.
The name of the PHP file whose content is the program that receives the message from the operator web page (Ajax function) is, operatorMessage.php. The program receives as data, the actual name of the operator and the message from the operator. The main portion of the program is:
//determine if conversation had already started
$selectDiscussionStr = "select * from discussion";
$DiscussionResult = mysql_query($selectDiscussionStr,$link);
$numOfRows = mysql_num_rows($DiscussionResult);
if ($numOfRows == 0)
{
$operatorStart = "started";
}
else
{
$operatorStart = "already";
}
//insert operator message into discussion table
$msgStr = "insert into discussion (Person, msg) values ("operator","$msg")";
mysql_query($msgStr,$link);
//select the last ID and use to update the discussion table
$selectStr = "select MAX(discussID) from discussion";
$IDResult = mysql_query($selectStr,$link);
$row = mysql_fetch_array($IDResult, MYSQL_NUM);
$discussID = $row[0];
//update cells for operator in the chartMonitor table
$updateOpStr = "update chartMonitor SET operatorStart = "$operatorStart", operatorMsgState = "new", lastOperatorID = "$discussID", operatorName="$operator" WHERE tableID=1";
mysql_query($updateOpStr,$link);
The program begins by reading all the data in the chartMonitor table. If the chartMonitor table does not exist ($numOfRows == 0), then a new declared variable, $operatorStart is given the value “started” otherwise it is given the value “already”. Remember, it is the operator that initiates conversation through this file, operatorMessage.php. In PHP a new variable declared can be seen outside the if-block.
Next, the program inserts the message and the word, “operator” in the discussion table. The program goes on to determine the ID of the row of the message just inserted. The message from the client and that from the operator are not inserted in the same row. The message from each person has its row and its ID in the same discussion table.
The last code segment in the above code updates the chartMonitor table. It gives the operatorStart cell the appropriate value. It gives the operatorMsgState cell the value, “new” so that the client PHP script that reads messages would use it to know that a current (new) message from the operator is available for reading.
When the operator is typing, an Ajax function in his web page calls the file, operatorWriting.php at the server. The data input to this file is the value, “Yes”. This file will not be called, if the operator is not typing. The main content of the file (program) is:
//update clientWriting cell for client in the chartMonitor table
$updateOpStr = "update chartMonitor SET operatorWriting = "$writing" WHERE tableID=1";
mysql_query($updateOpStr,$link);
It updates the operatorWriting cell of the chartMonitor table with the word, “Yes”, which is the value of $writing read from the input. When the value of this cell is read by the client interface program, the client interface program changes the value to “No”, to avoid false reading, the next time. However, the client ECMAScript will erase its text, “Agent is typing. . .”, only after it has actually read the completely typed message of the operator, from the server.
File for Ending Conversation
The operator ends the conversation. He clicks a button at the operator web page. This button calls an Ajax function at the web page. The Ajax function calls the file, dropTables.php at the server. The aim of this file (program) is to drop (erase) the two tables: chartMonitor and discussion. After deleting, it sends the feedback, “OK” to the operator web page. The operator web page uses this feedback to delete all the messages (conversation) that was displayed on its page. The main portion of the program at the server is:
//drop tables
$dropchartMonitorStr = "drop table chartMonitor";
$returnVal1 = mysql_query($dropchartMonitorStr,$link);
$dropDiscussionStr = "drop table discussion";
$returnVal2 = mysql_query($dropDiscussionStr,$link);
if (($returnVal1 == TRUE)&&($returnVal2 == TRUE))
{
echo "OK";
}
We have come to the end of this part of the series. The client also has a number of PHP interface files. The coding of the files are similar to these (above) of the operator. So it would be boring to explain them in this series. However, I give you the complete code at the end of the series.
We stop here and continue in the next part.
Chrys