Message system

Get Started
1

I am working on an online dating site. Members are able to send messages to other members, but they are as well able to send messages to moderators/administrators. To keep things clearly I would like to have a feature, that if it is an ongoing coversation between the member and the administrator where a certain moderator/administrator has answered a question and the member has replied to that answer, the answer will come back to the moderator/administrator that has answerd, but only if he/or she is online. Otherwise it should come in pool of unanswered messages for the other moderators/administrators that are online.

I am using PHP but would love to use Ajax as well so i don’t need any page refresh. Right now I have a page berichten (messages) in the admin panel. I am refreshing the page each 30 seconds using Ajax i.e.


$(function() {
    startRefresh();
});

function startRefresh() {
    setTimeout(startRefresh,30000);
    $.get('berichten.php', function(data) {
        $('#berichten).html(data);    
    });
}

and I have a table berichten (messages) with the following structure:


CREATE TABLE IF NOT EXISTS `berichten` (// messages
  `bericht_id` int(4) NOT NULL AUTO_INCREMENT,
  `member_id` smallint(4) NOT NULL, // The member who send the message
  `bericht` text,
  `admin` tinyint(2) NOT NULL DEFAULT '0', // This (on advise from Pullo) is to set the admin_id if he/she is online
  PRIMARY KEY (`bericht_id`),
  KEY `member_id` (`member_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

But from this point I really have no idea how to proceed? Any help would be highly appreciated

2

For the interacting of members with site staff, from your description you might be better off using a support ticket. A quick google search tuned up http://osticket.com/ i’ve never used that so can’t say how good it is. You could add some “bridging” so that when something is reported it generates a new support ticket in whatever support ticket software you go for.

3

Not the best idea to use AJAX that way.

Receiving new message in 30 seconds is way too slow and it’s possible that you’re wasting CPU cyble when there is no pending message.

Obviously, there are many solutions to this.

1 - Use WebSocket. This is 2 way connection that can push and pull requests from the server. You can show the messages at real time. I think there is a js framework called Socket.IO that makes it useable for older browser that doesn’t support Web Socket.

2 - Use long poll request. Don’t make the browser ping every 30 seconds. Have your server hold the request to retrieve the latest message. Until server gets a new message, it will continuously wait. This is how Facebook chat is doing.

Above is just a very brief description but you need to google for more details.

4

Hi sg707. Thank you for your reply and thoughts, very helpful. The 30 seconds was/is there because I really had/have no idea how to approach this differently. I find your second point very interresing though. Should I just search for long poll request? Or do you have any more information about this

5

http://book.mixu.net/node/ch3.html

Just google like

Long polling Ajax <Backend-end programming langulage like PHP/Java>

6

Although WebSockets / long polling would be good solutions, they may not always be practical. Depending on the OP’s server setup, using Node.js might not be an option (there’s also the PHP React library, but that may also not be practical on cheaper hosting solutions). From what I’ve read about long polling with PHP you’re holding open a thread, with the overhead that entails, which could eat up your server resources pretty quickly if you’ve a lot of users long polling simultaneously.

7

@ SpacePhoenix. The support ticket is not an option for the site owners.

I am looking into the suggestions made by sg707 and will see if I can implement that somehow later on. In the meantime I need to try to make it functioning as the site owners want is. This is what I have so far:

Table holding the messages:


CREATE TABLE IF NOT EXISTS `profiel_berichten` (
  `bericht_id` int(11) NOT NULL AUTO_INCREMENT,
  `profiel_id` smallint(4) NOT NULL DEFAULT '0',
  `operator_id` smallint(3) NOT NULL DEFAULT '0',
  `bericht` text,
  `verzonden` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
  `isNieuw` tinyint(1) NOT NULL DEFAULT '1',
  `bericht_type` tinyint(2) DEFAULT NULL,
  PRIMARY KEY (`bericht_id`),
  KEY `profiel_id` (`profiel_id`),
  KEY `operator_id` (`operator_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

And using the folloing queries:


    $c   = "SELECT * FROM profiel_berichten WHERE isNieuw = $isNieuw";
    $check = $pdo-&gt;prepare($c);
    $check-&gt;execute();
  
    $res = $check-&gt;fetchAll();

This first query I am using to determine if there are any new messages. When that is the case then run the message query


    if ($res) {
	  
	  $b = "SELECT profiel_id, bericht, operator_id 
	          FROM profiel_berichten
			 ORDER
			    BY profiel_id
			 ";
				
      $berichten = $pdo-&gt;prepare($b);		
	  $berichten-&gt;execute();	  
	  while($row = $berichten-&gt;fetch()) {
		  echo $row['bericht'];
	  }

The only thing I am missing in this second query is to see if this is a complete new message or a new message in an ongoing conversation and I was/am planning to use the operator_id for that purpose, something like:

if($row['operator_id '] &gt; 0){$b .= "WHERE operator_id = $_SESSION[operator_id]";};

But that is where I face a problem, because when it is a new message in an ongoing conversation where the profile has send an message to the staff the operator_id for that particular message is set to 0 (operator_id smallint(3) NOT NULL DEFAULT ‘0’). When a staff member reacts to that message the operator_id for the message sent by the operator will be set to 1. So the problem I have is that allthough it is an ongoing conversation, the status for operator_id in the messages send by the profile is set to 0 while the status for operator_id in messages sent by the operator is set to 1. But still I need the entire conversation as output.

How could I solve this first problem?

8

I didn’t get your last issue but I would remove the ‘isNew’ column and use ‘verzonden’ column to determine the new message.

Example, let say your ajax call is something like below

/rest/chat?lastUpdateDate=<current milli second>

then on serverside, you’ll create SQL that retreives all new messages after given time ‘lastUpdateDate’. Once you push this to the client, then it’ll send another request

/rest/chat?lastUpdateDate=<current milli second>

…then again…you may have issue with timezone since you’re using client’s timezone… Perhaps creating a chat sequence number column maybe better

/rest/chat?lastChatSeqNum=5

9