3.2 write_mysql.php

Close first_access.php and create a new PHP doc as 'write_mysql.php'.
Once user is logged in, (s)he can send messages to the other chat users by this file.

As usually; we will get the code first, and then the related comments:

            
<?
include_once ('config.inc.php');
// ---------------------
// get variables
// sent by flash
// ---------------------
$uniqid = $_GET['uniqid'];
$message = $_GET['message'];
// get data
$date = getmicrotime();
if(empty($message) || empty($uniqid))
{
   die();
}
// ---------------------
// Write data on mysql
// ---------------------
$query = "INSERT INTO $table (user_id, chat_data, chat_message) VALUES ('$uniqid', '$date', '$message')";
$sql = mysql_query($query,$db);
?>

          

At first, we get the variables passed by Flash:
$uniqid = $_GET['uniqid'];
$message = $_GET['message'];
and immediately after the actual time value.
Now if one of the required variables is empty, then the execution of script will be stopped by die();
If the code passes through this check, then the data will be written to MySQL table (flash_chat). This is a very simple query that assigns its value (uniqid, data, message) for each column in our MysQL table.

3.3 read_mysql.php

Once last file is saved and closed, we have to create the *real* PHP core file, which makes at least every operation needed to let our chat working!

Let's get into the code. This time I will divide the file into two parts, since it has more lines than previous ones.

- PART 1

            
<?
if(!isset($_GET['uniqid']))
{
   die("output=false");
}
// ---------------------
// include the
// configuration file
// ---------------------
include_once ('config.inc.php');
$a = 0;
$uniqid = $_GET['uniqid']; // recognize chat user
// ------------------------
// GET MY LAST UPDATE
// ------------------------
$query = "SELECT user_date FROM $user WHERE user_id = '$uniqid' LIMIT 1";
$sql = mysql_query($query, $db);
$row = mysql_fetch_assoc($sql);
$last_time = $row['user_date'];
mysql_free_result($sql);
// ------------------------
// UPDATE whos_online TABLE
// ------------------------
$time_start = getmicrotime();
$query = "UPDATE $user SET user_date = '$time_start' WHERE user_id = '$uniqid' LIMIT 1";
@$sql = mysql_query($query,$db)
?>

          

OK, let's start...
A t first check, if the variable is defined by 'uniqid', then it can be passed by Flash. In other case, the script will be stopped.

The following lines will provide us the information when the client made its last update into MySQL db:

// ------------------------
  // GET MY LAST UPDATE
  // ------------------------
  $query = "SELECT user_date FROM $user WHERE user_id = '$uniqid' LIMIT 1";
  $sql = mysql_query($query, $db);
  $row = mysql_fetch_assoc($sql);
  $last_time = $row['user_date'];
  mysql_free_result($sql); 

Now, we store that value into the $last_time variable, therefore we can now make the update with this new query:

$time_start = getmicrotime();
  $query = "UPDATE $user SET user_date = '$time_start' WHERE user_id = '$uniqid' LIMIT 1";
  @$sql = mysql_query($query,$db); 

 

- PART 2

            
<?
// -----------------------
// if is the first query
// return history
// -----------------------
if(isset($_GET['first_run']))
{
   $limit = "LIMIT $first_run";
   $clause = "WHERE chat.user_id = user.user_id";
} else {
   // -----------------------------
   // else return only new messages
   // -----------------------------
   $limit = "";
   $clause = "WHERE chat.user_id != '$uniqid' AND chat.user_id = user.user_id
   AND $last_time < chat.chat_data";
}
// ---------------------
// QUERY FROM TABLE
// ---------------------
$query = "SELECT chat.chat_data, chat.chat_message, user.user_name
FROM $table as chat, $user as user $clause ORDER by chat.chat_data DESC $limit";
if(@$sql = mysql_query($query,$db))
{
   $_totalmsg = mysql_num_rows($sql);
   while($row = mysql_fetch_assoc($sql))
   {
      $a++;
      $data = urlencode($row['chat_data']);
      $username = urlencode($row['user_name']);
      $message = urlencode(htmlspecialchars($row['chat_message']));
      print "&__date$a=$data&__user$a=$username&__message$a=$message";
   }
}
print "&__total=$_totalmsg";
// -----------------------
// Tell flash how many
// items in the array
// -----------------------
if(isset($_GET['first_run']))
{
   print "&__array_length=$first_run";
}
// -----------------------
// NOW RETURN ONLINE
// USERS
// -----------------------
$query = "SELECT user_name FROM $user
WHERE ($time_start - user_date) < $active ORDER BY user_name ASC";
if(@$sql = mysql_query($query,$db))
{
   $a = 0;
   while($row = mysql_fetch_assoc($sql))
   {
      $a++;
      print "&__online$a=$row[user_name]";
   }
}
mysql_free_result($sql);
?>

          

Note also these lines:

if(isset($_GET['first_run']))
  {
  $limit = "LIMIT $first_run";
  $clause = "WHERE chat.user_id = user.user_id";
  } else {
  // -----------------------------
  // else return only new messages
  // -----------------------------
  $limit = "";
  $clause = "WHERE chat.user_id != '$uniqid' AND chat.user_id = user.user_id
  AND $last_time < chat.chat_data";
  } 

If Flash passes the var first_run, then the query will be different from all of the preceeding ones. Actually Flash calls these variables just for the first time to get the chat history and then gets only new messages.

A simple while is made to print the output for the informations which will be used by Flash. The last part of the file is related with the query that checks online users and passes the information again to Flash after the previous output.

$query = "SELECT user_name FROM $user WHERE ($time_start - user_date) < $active ORDER BY user_name ASC"; 

Here it is... If now ($time_start) - the time value existing on db is less than the value of $active (20000 by default), then it is assumed that users are still online in the chat.

When Flash calls this page by using a setInterval and a LoadVars function, this page will return an output such as:

&__date1=1019745202478&__user1=Gianni&__message1=Hi+everyone&__total=12&__online1=Gianni