Php – Redis sub/pub and php/nodejs

MySQLnode.jsPHPredis

Starting to work on a new project using redis as a sub/pub system to display results from a mysql db. So if there are updates I wanted to publish those updates from mysql to my web page. My question is, which option would be better?

Option 1:
Should I just do all of that via nodejs and socket.io? Meaning creating a nodejs script that connects to redis, subscribe to the channels I need to listen to, using mysql in nodejs query the db for updates, if updates publish the mysql rows then in the html that is connecting to nodejs via socket.io get the new data and process it to display the results?

Option 2:
Have a php script query mysql and with redis-php client publish any updates to the channel? Dont know exactly what else needs to be setup from here. Do I still need to have nodejs involved in this option?

Or am I just off based on how all this works? The bottom line is I want to display results via mysql database to the user using redis sub/pub capabilities.

Best Answer

Option 3

When you update MySQL from PHP you publish those changes to node.js via redis publish command(publish from PHP when mutating database). From node.js I would receive those changes in real-time thanks to Redis's subscribe. Then I would just broadcast them to users interested via socket.io. You could for example publish to channel mysql. Take for example the following SQL statement => INSERT INTO comments (1, "Hello World"). Where 1 is something like userid, and Hello World would be something like the comment. I probably would not publish SQL-statement to that channel, but JSON instead which I can easily use both from JavaScript(JSON.stringify / JSON.parse) and PHP(json_encode / json_decode).

Update

You don't run a cron-job because this would defeat the purpose off Redis's pubsub. Take for example I visit your website which is a blog at http://localhosts. I read an article at http://localhost.com/a.php. Below on the site you provide a form which I can use to post a comment to that article:

a.php

<html>
<head>
    <title>Interesting blog post</title>
</head>
<body>
    <div id="article">This is interesting</div>

    <div id="comments">
        <div class="comment">
            <div class="from">Alfred Said at 22:34</div>
            <div class="message">Hello World</div>
        </div>
    </div>

    <form action="post.php" method="post">
        <label for="name">Your name</label><br />
        <input type="name" id="name" name="name" /><br />

        <label for="message">Your Message:</label><br />
        <textarea id="message" name="message"></textarea>

        <input type="submit" />
    </form>


    <script src='jquery.min.js'></script>
    <script src='http://localhost:8888/socket.io/socket.io.js'></script>
    <script type="text/javascript">
        $(document).ready(function () {
                var socket = io.connect('http://localhost:8888');

                socket.on('message', function (json) {
                    var obj = $.parseJSON(json);
                    alert('in here: ' + obj.name);
                });
        });
    </script>
</body>
</html>

I submit the form which has action attribute http://localhost/postcomment.php. But this is the important part! At post.php you retrieve the data I posted and insert it into MySQL using INSERT INTO comments (1, "Hello World"). When this mutation happens you also need to inform node.js process which is continually listening to channel mysql:

post.php:

<?php

$_POST  = filter_input_array(INPUT_POST, FILTER_SANITIZE_STRING);

require("./Predis.php");
$redis = new Predis\Client();
$obj = array(
    'name'      => $_POST['name'],
    'message'   => $_POST['message']
);

$json = json_encode($obj);
$redis->publish("mysql", $json);

echo $json;

post.php requires predis.

The node code with node_redis would look something like:

var redis       = require('redis'),
    subscriber  = redis.createClient(),
    express     = require('express'),
    store       = new express.session.MemoryStore(),
    app         = express.createServer(
        express.bodyParser(),
        express.static(__dirname + '/public'),
        express.cookieParser(),
        express.session({ secret: 'htuayreve', store: store}))
    sio         = require('socket.io');

app.listen(8888, '127.0.0.1',  function () {
    var addr = app.address();
    console.log('app listening on http://' + addr.address + ':' + addr.port);
});

var io = sio.listen(app);

io.configure(function () {
    io.set('log level', 1); // reduce logging
});

io.sockets.on('connection', function (socket) {
    socket.join('mysql');   
    socket.on('disconnect', function () {
    });
});

subscriber.on('message', function (channel, json) {
    // this will always retrieve messages posted to mysql
    io.sockets.in('mysql').json.send(json);
});

subscriber.subscribe('mysql');

This samples depends on the following packages, which you can install via npm

npm install socket.io
npm install redis
npm install express

Always when I post the form post.php, I also publish these changes to redis. This part is important! The node.js process is always receiving those changes thanks to Redis's pubsub. Every time when a php script mutates the database you should publish these changes to Redis with publish.

P.S: Hope this is clear. Maybe later when I have some time available I update with maybe little snippet...

Related Topic