The chat room script below does one thing. It gives users a display of the most recent comments in the chat room when they start.

On to the PHP code. As usual, we start with config.php, since without it, the MySQL-based app would not be viable. You cannot relate to a db without knowing the magic words. Then we define some arrays we'll be needing to organize the chat entries we'll be dealing with. Next we GET the username from the PHP script that sent us to this script, namely cms-chat-room.php. Then we update this user's MySQL db table data so there is no private chat indicated. You start the chat room in Public mode and can only get to Private chat mode when one asks for it and the other agrees. So priv_if gets a zero and priv_who gets an empty string. This is in case you left the last chat session while still in Private chat mode so these table fields were indicating this fact.

In relating to time, we use date_default_timezone_set('UTC') to set the time zone to Coordinated Universal Time, which is UTC, and them subtract 64 seconds and add 14400 seconds. The format is strtotime("now")-64+14400 to get 64 seconds ago and this is compared to the UNIX_TIMESTAMP() value of the datetime numbers we store in our db table. (In the other chat room scripts we substract only 14 seconds, and this seems to be enough to get all entries that are wanted. The AJAX in the chat room home page goes out to a different PHP script (different from this PHP script) every 8 seconds and gets the last 14 seconds worth of entries, throwing out duplicates that happen because we use 14 rather than 8 seconds in the entry getting routine. We do this this way because 14 seconds is the minimum required offset to avoid skipping some entries altogether, which occurs because of all the server action, code running action, etc., so we get dropouts if we don't use 14 instead of 8 seconds.) The 14400 in the code below is because our server's time is 4 hours ahead of UTC. Both the SQL function UNIX_TIMESTAMP() and the PHP strtotime() function give the number of seconds since 1970, so they're comparable. We use datetime values, not timestamps, but the timestamp function shown is the right one for conversion—perhaps the only SQL function that will do the needed job. When we display times in the chat window, we use the UTC convention many other chat rooms like to use, since the chatters are likely to be from many different time zones, and what sense would it make to have a 10pm comment followed by a 7pm comment followed by a 9pm comment, if we could even determine which zone they were in? Giving everyone a UTC time gives consistancy and people learn to read only minutes and seconds and ignore the hours.

When the code below relates to time, it checks a table called changed to see if there has been any entries in the last 64 seconds. We use 64 ONLY in this start.php file, and that's because we're getting this late joiner a good glimpse at all the recent chat action—if we had used 14, there may have been no comments so the chat window would be empty. If there has been any action, the script goes on. If not, it goes back to the AJAX routine that called it in the cms-chat-room.php script—the chat room home page.

On with the script. The datetime0, chat0, and screen_name fields that get consulted will be followed with datetime1, chat1, and screen_name; datetime2, chat2, and screen_name; datetime3, chat3, and screen_name; and datetime4, chat4, and screen_name field consulting later on in the script. This is because there are 5 chat datetime and chat entry fields for each user mostly because it is possible for a user to enter more than one comment every 8-14 seconds so this prevents dropped entries. Note that the condition priv_if = '0' is to make sure only Public chat entries are displayed to this latecomer. The code priv_if = '1' would have gotten us only Private chats, and no priv_if conditional statement would have gotten us both. The PHP while() loop is because there may be many users whose chat0 fields (in this code block—the other fields are processed in code blocks lower down) were active in the last 14 seconds. Note that for each entry that fills the criteria, we store the datetime, time, screen name, and chat entry in our predefined arrays. The mysql_real_escape_string() function escapes the entry content, then the htmlentities() function makes this content safe to display. Then the double quote, greater than and less than characters are turned from html entities to their original form, because these are needed for various tags and emoticon names without which we could not use emoticons or give users the ability to have links, email links, bold, underline, or italics in their comments. We make them use OUR tag syntax though, for security, as you'll see.

Anyway, once all 5 chat fields are perused for each online user's recent comments, this innocent sounding line appears before our wondering eyes:
array_multisort($datetimes, $times, $names, $blabber);.
This gem sorts the last 3 arrays ($times, $names, $blabber) parallel to the sorting of the $datetimes array which now gets sorted from early to late. This means the arrays we actually need, $times, $names, $blabber, are now representing parallel record data that is sorted early to late, i.e., now $times[9], $names[9], $blabber[9] are from the same db table record.

Next we concatenate the record data into one array separated by | characters. We'd use array_unique() now but it has a huge bug and will not work so we use the functions array_keys() and array_count_values() and array_values() and we've now dumped duplicate values and fixed up the element numbers so the holes get filled. Next, we explode() the array values into separate arrays, dumping the | characters as we go. The reason we had to concatenate and then "unconcatenate" is that there is no multi_array_unique() function to deal with parallel array values from db records, and even if there was, it wouldn't work. We don't know why the PHP people have problems with duplicate dumping. It's quite a simple operation as you can see in this JavaScript version we came up with: remove-duplicates-in-array.html.

So once we have the final array values representing the chat data we want to display, we echo them to the AJAX routine on the chat home page as a concatenated series of chat comments with time and screen name with them so you know who said what when. You might notice that if the comment found is "._..q...v_.z__._k_." we dump the comment. That's because it's just a code that is not meant to be seen. It's used when a user is asking another user if s/he wants a private chat and there really is no message since we don't want this solicitation going to anyone but the intended recipient, and we do that in the chat room's Online Now box on the right side of the screen—the box that show who is logged in. The actual code logic whereby one user asks another if s/he wants to chat in private is handled by a combination of JavaScript flags, PHP flags, and db table field flag values. The $q=$q.$qq concatenation shows the comments all being stuck together for one big echo as $q. This echo is sent to the AJAX xmlhttp.responseText in the chat room home page where it is processed before display.

Save this file as start.php

<?php

include_once"config.php";

$datetimes=array();$times=array();$blabber=array();$names=array();

$U=$_GET['u'];

mysql_query("UPDATE chatroommembers SET priv_if = '0', priv_who = '' WHERE username='$U'") or die('Error ,saving failed');

date_default_timezone_set('UTC');
$currentminus64=strtotime("now")-64+14400;//14400=4hrs different TZ; others' servers may be different
$result = mysql_query("SELECT date_changed FROM changed WHERE UNIX_TIMESTAMP(date_changed) > '$currentminus64'") or die('Error ,saving failed');
if(mysql_num_rows($result)>0){

$result = mysql_query("SELECT datetime0,chat0,screen_name FROM chatroommembers WHERE UNIX_TIMESTAMP(datetime0) > $currentminus64 AND priv_if = '0' order by datetime0") or die(mysql_error());
while($row = mysql_fetch_array($result)){
array_push ($datetimes, $row['datetime0']);
array_push ($names, $row['screen_name']);
$t=$row['datetime0'];
$t=substr($t,11,8);
array_push ($times, $t);
$chatter=mysql_real_escape_string($row['chat0']);
$chatter=htmlentities(stripslashes($chatter), ENT_QUOTES);
$chatter=str_replace('"', '"', $chatter);
$chatter=str_replace('<', '<', $chatter);
$chatter=str_replace('>', '>', $chatter);
array_push ($blabber, $chatter);
}

$result = mysql_query("SELECT datetime1,chat1,screen_name FROM chatroommembers WHERE UNIX_TIMESTAMP(datetime1) > $currentminus64 AND priv_if = '0' order by datetime1") or die(mysql_error());
while($row = mysql_fetch_array($result)){
array_push ($datetimes, $row['datetime1']);
array_push ($names, $row['screen_name']);
$t=$row['datetime1'];
$t=substr($t,11,8);
array_push ($times, $t);
$chatter=mysql_real_escape_string($row['chat1']);
$chatter=htmlentities(stripslashes($chatter), ENT_QUOTES);
$chatter=str_replace('"', '"', $chatter);
$chatter=str_replace('<', '<', $chatter);
$chatter=str_replace('>', '>', $chatter);
array_push ($blabber, $chatter);
}

$result = mysql_query("SELECT datetime2,chat2,screen_name FROM chatroommembers WHERE UNIX_TIMESTAMP(datetime2) > $currentminus64 AND priv_if = '0' order by datetime2") or die(mysql_error());
while($row = mysql_fetch_array($result)){
array_push ($datetimes, $row['datetime2']);
array_push ($names, $row['screen_name']);
$t=$row['datetime2'];
$t=substr($t,11,8);
array_push ($times, $t);
$chatter=mysql_real_escape_string($row['chat2']);
$chatter=htmlentities(stripslashes($chatter), ENT_QUOTES);
$chatter=str_replace('"', '"', $chatter);
$chatter=str_replace('<', '<', $chatter);
$chatter=str_replace('>', '>', $chatter);
array_push ($blabber, $chatter);
}

$result = mysql_query("SELECT datetime3,chat3,screen_name FROM chatroommembers WHERE UNIX_TIMESTAMP(datetime3) > $currentminus64 AND priv_if = '0' order by datetime3") or die(mysql_error());
while($row = mysql_fetch_array($result)){
array_push ($datetimes, $row['datetime3']);
array_push ($names, $row['screen_name']);
$t=$row['datetime3'];
$t=substr($t,11,8);
array_push ($times, $t);
$chatter=mysql_real_escape_string($row['chat3']);
$chatter=htmlentities(stripslashes($chatter), ENT_QUOTES);
$chatter=str_replace('"', '"', $chatter);
$chatter=str_replace('<', '<', $chatter);
$chatter=str_replace('>', '>', $chatter);
array_push ($blabber, $chatter);
}

$result = mysql_query("SELECT datetime4,chat4,screen_name FROM chatroommembers WHERE UNIX_TIMESTAMP(datetime4) > $currentminus64 AND priv_if = '0' order by datetime4") or die(mysql_error());
while($row = mysql_fetch_array($result)){
array_push ($datetimes, $row['datetime4']);
array_push ($names, $row['screen_name']);
$t=$row['datetime4'];
$t=substr($t,11,8);
array_push ($times, $t);
$chatter=mysql_real_escape_string($row['chat4']);
$chatter=htmlentities(stripslashes($chatter), ENT_QUOTES);
$chatter=str_replace('"', '"', $chatter);
$chatter=str_replace('<', '<', $chatter);
$chatter=str_replace('>', '>', $chatter);
array_push ($blabber, $chatter);
}

array_multisort($datetimes, $times, $names, $blabber);

$num=count($datetimes);

for ($i=0;$i<$num;$i++) {$datetimes[$i]=$times[$i]."|".$names[$i]."|".$blabber[$i];}
$datetimes=array_keys(array_count_values($datetimes));
//array_unique() has huge bug and will not work!!!!!!!!!!
$datetimes=array_values($datetimes);
$num=count($datetimes);$q="";

for ($i=0;$i<$num;$i++) {
$a=explode("|", $datetimes[$i]);
$qq="|"."($a[0]) <b>$a[1]:</b> $a[2]<br>";
if($a[2]=="._..q...v_.z__._k_."){$qq="";}
$q=$q.$qq;}
echo $q;

mysql_close();
}
?>