Real time chat, message handling - Socket.io, PHP, MySQL, Apache

Question

I am a beginner when it comes to web development. Recently i have been working on a real time chat website based completely on PHP and JS/jQuery (i'm not using any frameworks). Currently, my setup is just simple AJAX polling, which obviously isn't as good as i'd like it to be. My database is a MYSQL database.

I have read about websockets and my new initial plan was to create a NodeJS server with Socket.io which will handle messages (How to integrate nodeJS + Socket.IO and PHP?), and i thought about storing those messages in a MySQL database (MySQL with Node.js).

Here is what i have currently (not much, i'd like to clarify how to progress before i actually do progress). This is my test setup, the HTML used in actual chat is a bit different obviously.

Node.js Server:

// NODE
var socket = require( 'socket.io' );
var express = require( 'express' );
var https = require( 'https' );
var http = require( 'http'); //Old
var fs = require( 'fs' );

var app = express();

//Working HTTPS server 
var server = https.createServer({ 
               key: fs.readFileSync('/etc/letsencrypt/live/%site%/privkey.pem'),
               cert: fs.readFileSync('/etc/letsencrypt/live/%site%/fullchain.pem')
             },app);

// var server = https.createServer( app ); Won't work cause no cert. 

var io = socket.listen( server );
console.log("Server Started"); 
io.sockets.on( 'connection', function( client ) {
    console.log( "New client !" );

    client.on( 'message', function( data ) {
        console.log( 'Message received ' + data); //Logs recieved data
        io.sockets.emit( 'message', data); //Emits recieved data to client.
    });
});
server.listen(8080, function() {
    console.log('Listening');
});

JS Client script:

var socket = io.connect('https://%site%:8080');



document.getElementById("sbmt").onclick = function () {

socket.emit('message', "My Name is: " + document.getElementById('nameInput').value + " i say: " + document.getElementById('messageInput').value); 

};

socket.on( 'message', function( data ) {
    alert(data); 
    });

My super-simple test HTML:

<form id="messageForm">
<input type="text" id="nameInput"></input>
<input type="text" id="messageInput"></input>
<button type="button" id="sbmt">Submits</button>
</form>

PHP requires a bit explanation - At the moment when someone connects to my website i run session_start(). This is because i want to have something like anonymous sessions. I distinguish between logged in and anonymous users through $_SESSION variables. An anon user will have $_SESSION['anon'] set to true, as well as will NOT have $_SESSION['username'] set. Logged in user will obviously have it inverted.

When it comes to the chat - it's available to both logged in users as well as anonymous users. When user is anonymous, a random username is generated from a database or random names. When user is logged in, his own username is chosen. Right now my system with Ajax polling works like this:

User inputs the message (in the current chat solution, not the testing HTML i sent above) and presses enter and an AJAX call is made to following function:

  function sendMessage($msg, $col) {
    GLOBAL $db;
      $un = "";


    if (!isset($_SESSION['username'])) {

        $un = self::generateRandomUsername();

    } else {
    $un = $_SESSION['username'];
    }

    try {
      $stmt = $db->prepare('INSERT INTO chat (id, username, timestamp, message, color) VALUES (null, :un, NOW(), :msg, :col)');
      $stmt->bindParam(':un', $un, PDO::PARAM_STR);
      $stmt->bindValue(':msg', strip_tags(stripslashes($msg)), PDO::PARAM_STR); //Stripslashes cuz it saved \\\ to the DB before quotes, strip_tags to prevent malicious scripts. TODO: Whitelist some tags. 
      $stmt->bindParam(':col', $col, PDO::PARAM_STR);
        } catch (Exception $e) {
            var_dump($e->getMessage());
    }
      $stmt->execute();
  }

(Please don't hate my bad code and crappy exception handling, this is not any official project). This function inputs users message to the database.

To recieve new messages, i use setTimeout() function of JS, to run an AJAX check every 1s after new messages. I save the ID of last message that is displayed in JS, and send that ID as a parameter to this PHP function (and it's ran every 1s):

  /* Recieve new messages, ran every 1s by Ajax call */
  function recieveMessage($msgid) {
    //msgid is latest msg id in this case
    GLOBAL $db;
    $stmt = $db->prepare('SELECT * FROM chat WHERE id > :id');
    $stmt->bindParam(':id', $msgid, PDO::PARAM_INT);
    $stmt->execute(); 
    $result = $stmt->fetchAll(PDO::FETCH_ASSOC);
    return json_encode($result);

  }

The question is: How to implement something similar, but with my earlier mentioned setup of node.js server and websockets? I need to distinguish between logged in and anonymous users somehow. My first idea was to just run an ajax call from node.js server to PHP and pass message data, and PHP will insert it into DB exactly as it does right now. But the problem in this case is how to send the message out to the clients again? Usernames are applied while the message is being input into database, that means i'd have to call AJAX to save to the DB, and then call another AJAX to extract the newly input message and emit it to the clients, or make a function that inserts and extracts and returns extracted message. However, won't that cause problems when 2 messages are input at the exactly same time?

Is it somehow possible to access PHP session variables in Node.js? Then i could rewrite all DB querying to work in the Node.js server instead of PHP.

I apologize once more if my code or explanation is messy.

Answer 1

SO, for everyone that is wondering and will find this thread in the future: I DID NOT FIND AN ANSWER WITH THE SOLUTION I WANTED TO USE, HOWEVER I CAME UP WITH SOMETHING ELSE, AND HERE IS A DESCRIPTION:

Instead of making Node.js server send the AJAX request, i left it as i had before, the jQuery $.post() request from the client, to a PHP function.

What i did next was to implement a MySQL listener, that checked the MySQL binlog for changes. I used mysql-eventsmodule. It retrieves the newly added row with all data and then uses socket.io emit function to send it to connected clients. I also had to drop SSL because it apparently hates me. It's a small hobby project, so i don't really have to bother that much with SSL.

Best solution would be obviously to program the whole webserver in Node.js and just drop Apache completely. Node.js is awesome for real time applications, and it's a very easy language to learn and use.

My setup of Node.js + Socket.io + mysql-events: (ignore the unused requires)

// NODE
var socket = require( 'socket.io' );
var express = require( 'express' );
var https = require( 'https' );
var http = require( 'http');
var fs = require( 'fs' );
var request = require( 'request' );
var qs = require( 'qs' );
var MySQLEvents = require('mysql-events');

var app = express();


/*Correct way of supplying certificates.
var server = https.createServer({
               key: fs.readFileSync('/etc/letsencrypt/live/x/privkey.pem'),
               cert: fs.readFileSync('/etc/letsencrypt/live/x/cert.pem'),
               ca: fs.readFileSync('/etc/letsencrypt/live/x/chain.pem')
       },app); */

var server = http.createServer( app ); // Won't work without cert.

var io = socket.listen( server );
console.log("Server Started");

//DB credentials
var dsn = {
  host:     'x',
  user:     'x',
  password: 'x',
};
var mysqlEventWatcher = MySQLEvents(dsn);

//Watcher magic, waits for mysql events.
var watcher = mysqlEventWatcher.add(
  'newage_db.chat',
  function (oldRow, newRow, event) {

     //row inserted
    if (oldRow === null) {
      //insert code goes here
      var res = JSON.stringify(newRow.fields); //Gets only the newly inserted row data
    res.charset = 'utf-8'; //Not sure if needed but i had some charset trouble so i'm leaving this. 
      console.log("Row has updated " + res);
      io.sockets.emit('message', "[" + res + "]"); //Emits to all clients. Square brackets because it's not a complete JSON array w/o them, and that's what i need. 
    }

     //row deleted
    if (newRow === null) {
      //delete code goes here
    }

     //row updated
    if (oldRow !== null && newRow !== null) {
      //update code goes here
    }

    //detailed event information
    //console.log(event)
  });

io.sockets.on( 'connection', function( client ) {
    console.log( "New client !" );



    client.on( 'message', function( data ) {
        //PHP Handles DB insertion with POST requests as it used to.
    });
});
server.listen(8080, function() {
    console.log('Listening');
});

Client JavaScript SEND MESSAGE:

$('#txtArea').keypress(function (e) {

  if (e.which == 13 && ! e.shiftKey) {

      var emptyValue = $('#txtArea').val();
      if (!emptyValue.replace(/\s/g, '').length) { /*Do nothing, only spaces*/ }
      else {
            $.post("/shana/?p=execPOST", $("#msgTextarea").serialize(), function(data) {

            });


  }

  $('#txtArea').val('');
  e.preventDefault();
}


});

Cliend JavaScript RECIEVE MESSAGE:

socket.on( 'message', function( data ) {
          var obj = JSON.parse(data);

          obj.forEach(function(ob) {
          //Execute appends

          var timestamp = ob.timestamp.replace('T', ' ').replace('.000Z', '');
          $('#messages').append("<div class='msgdiv'><span class='spn1'>"+ob.username+"</span><span class='spn2'style='float: right;'>"+timestamp+"</span><div class='txtmsg'>"+ob.message+"</div>");
          $('#messages').append("<div class='dashed-line'>- - - - - - - - - - - - - - - - - - - - - - - - - - -</div>"); //ADD SCROLL TO BOTTOM
          $("#messages").animate({ scrollTop: $('#messages').prop("scrollHeight")}, 1000);
        });
    });

Somehow, the binlog magic destroys the timestamp string, so to clean it up i had to replace a bit of the string itself.

PHP DB INSERT FUNCTION:

  function sendMessage($msg, $col) {
    GLOBAL $db;
      $un = "";


    if (!isset($_SESSION['username'])) {

        $un = self::generateRandomUsername();

    } else {
    $un = $_SESSION['username'];
    }
    try {
      $stmt = $db->prepare('INSERT INTO chat (id, username, timestamp, message, color) VALUES (null, :un, NOW(), :msg, :col)');
      $stmt->bindParam(':un', $un, PDO::PARAM_STR);
      $stmt->bindValue(':msg', strip_tags(stripslashes($msg)), PDO::PARAM_LOB); //Stripslashes cuz it saved \\\ to the DB before quotes, strip_tags to prevent malicious scripts. TODO: Whitelist some tags.
      $stmt->bindParam(':col', $col, PDO::PARAM_STR);
        } catch (Exception $e) {
            var_dump($e->getMessage());
    }
      $stmt->execute();
  }

I hope this helps someone at least a bit. Feel free to use this code, as i probably copied most of it from the internet already anyway :) I will be checking this thread from time to time, so if you have any questions leave a comment.