How to show data from mysql in nodejs with a refresh rate

Question

I'd like to show data from MySQL using nodejs with a refresh rate. I've done the following coding and getting result. But the problem is if I open localhost:8000/?id=1 on the browser in one tab it's showing data from MySQL for id 1, but if I open localhost:8000/?id=2 on another tab, it shows data for id 2 but at the same time, the first tab begins to show data for id 2 also. I need to show data from MySQL for the specific id in URL on different tabs.

in server.js I wrote


    var app = require('http').createServer(handler),
    url = require("url"),
    io = require('socket.io').listen(app),
    fs = require('fs'),
    mysql = require('mysql'),
    connectionsArray = [],
    connection = mysql.createConnection({
        host: 'localhost',
        user: 'root',
        password: '',
        database: 'nodetest'
    }),
    POLLING_INTERVAL = 3000,
    pollingTimer;
    connection.connect(function(err) {
        console.log(err);
    }); 
    app.listen(8000); 
    var id;
    function handler(req, res) {
        var url_parts = url.parse(req.url, true);
        var query = url_parts.query;
        if (query.id) {
            id = query.id;
            console.log(id);
        }
        fs.readFile('client.html',
         function(err, data) {
            if (err) {
                console.log(err);
                res.writeHead(500);
                return res.end('Error loading client.html');
            }
            res.writeHead(200);
            res.end(data);
        });
    }
    var pollingLoop = function() { 
        var query = connection.query('SELECT * FROM users WHERE id = ' + id),
        users = []; 
        query.on('error',
        function(err) { 
            console.log(err);
            updateSockets(err);
        }).on('result',
        function(user) { 
            users.push(user);
        }).on('end',
        function() {
            if (connectionsArray.length) {
                pollingTimer = setTimeout(pollingLoop, POLLING_INTERVAL);
                updateSockets({
                    users: users
                });
            }
        });
    }; 
    io.sockets.on('connection',
    function(socket) {
        console.log('Number of connections:' + connectionsArray.length); 
        if (!connectionsArray.length) {
            pollingLoop();
        }
        socket.on('disconnect',
        function() {
            var socketIndex = connectionsArray.indexOf(socket);
            console.log('socket = ' + socketIndex + ' disconnected');
            if (socketIndex >= 0) {
                connectionsArray.splice(socketIndex, 1);
            }
        });
        console.log('A new socket is connected!');
        connectionsArray.push(socket);
    });
    var updateSockets = function(data) { 
        data.time = new Date(); 
        connectionsArray.forEach(function(tmpSocket) {
            tmpSocket.volatile.emit('notification', data);
        });
    };

And in client.html i wrote

<html>
<head>
<script src="socket.io/socket.io.js"></script> 
<script src="http://code.jquery.com/jquery-latest.min.js"></script> 
<script type="text/javascript">
var socket = io.connect('http://localhost:8000'); 
socket.on('notification',
function(data) {
    var usersList = "<div>";
    $.each(data.users,
    function(index, user) {
        usersList += "<div><p><span style='width:100px;float:left;text-align:center'>Name :</span>" + user.name + "</p></div>" + "<div><p><span style='width:100px;float:left;text-align:center'>First Name :</span>" + user.flane + "</p></div>" + "<div><p><span style='width:100px;float:left;text-align:center'>Last Name :</span>" + user.lname + "</p></div>";
    });
    usersList += "</div>";
    $('#container').html(usersList);
});
</script>
</head>
<body>
<div id="container">Loading ...</div>
</body>
</html>

The problem here is that you are settings id everytime with same variable, so maybe instead of using id on global level, write that to socket.selecetedID and query from there :) — halilcakar

shawnzhu shawnzhu · Accepted Answer · 2013-07-06T11:00:41

You should not specify the variable id in line #19 of the server side javascript. If you do so, your HTTP handler's job is updating this global visible variable via query string. As a result, the last HTTP GET request with query string ?id=x will update the variable id to be x.

Instead, you should use a map like ids and push value into it when anyone hit GET request with query string like ?id=x, then work with this map object in your function pollingLoop.

However, it still doesn't fit your requirement where the current solution likes broadcasting very much since it can not provide data per session. You may take a look at session.socket.io.

How to show data from mysql in nodejs with a refresh rate

1 Answers