How to access mysql database with socket.io

Question

I'm just getting into coding server side javascript and have been reading tutorials on socket.io and node.js, but I haven't come across anything demonstrating how to use node.js to access a mysql database.

Say for instance I want to create a method that listens to a table in my database at mysql.something.com (with database: database, username: username, etc), how would I get socket.io with node.js to connect to that database and listen for new input to that table and then subsequently return that input?

I'm wondering if anyone could give me a specific example that uses a publish subscribe model.

Thanks for the help.

Socket.IO can't. You need to use Node's MySQL module (`npm install mysql` IIRC) and use that. It sounds like you've misunderstood the basic structure — Bojangles, Jul 12 '13 at 15:25

score 2 · Accepted Answer · answered Jul 12 '13 at 16:12

2

You have to poll mysql database for changes at regular interval and when detect a change emit a socket.io event. Here's a pseudo code

var mysql = require('mysql');
var connect = mysql.createConnection({
      host: 'localhost'
    , database: 'your_database'
    , username: 'user'
    , password: 'password'});

var initial_result;

// check for changes after 1 second

setTimeout(function(){

    connect.query('select * from your_table', function(err, result) {
        if(err) { throw new Error('Failed');}
        initial_result = initial_result || result;

        if(Changed(initial_result, result)) { socket.emit('changed', result); }

    });

    function Changed(pre, now) {
  // return true if pre != now
    }


}, 1000);

answered Jul 12 '13 at 16:12

az7ar

5,187
2
19
23

3

Thanks for the example but I was kind of looking for something that used more of a publish subscribe model rather than a polling one. – Gavin Sellers Jul 12 '13 at 16:23
As far as I know Mysql does not have that sort of feature – az7ar Jul 12 '13 at 21:30
can i do this by using mongodb. If i give setinterval means it will get database frequently. – Vinoth Kumar Jan 24 '14 at 14:53
Sorry for the stupid question but having a JS file like this making queries to the database LIVE and having your database login in a js file...? How secure is this? – Jack Apr 28 '15 at 20:54
1

This is Node.js code ( server side js ) and recides on your server, not client ( i.e. web browser ). – az7ar May 16 '15 at 11:42

How to access mysql database with socket.io

1 Answers1