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.

Answer 1

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);