Track Changes to MySQL table using Node.js

Question

I am trying to integrate node.js app into an existing PHP/MySQL system. node-mysql is used to allow node.js to access the MySQL database. A table named tableA in the MySQL database updates with new data every 10 minutes.

Problem: Using node, I need the node server to continuously check tableA for changes whenever tableA gets updated. What is a good way to achieve this?

I am thinking if I used setInterval(checkDb(tableA), 10*60*1000), or an infinite loop, the checkDbquery function may not coincide with the database update.

I also happen to be using socket.io so will a good method be for the PHP system to emit('dbUpdated') to the node server if it is even possible for PHP to send a message to node.js?

Node Code

var dbCheckQueue = function(callback) {
    client.query('SELECT * from tableA',
                    function(error, results, fields) {
                        if error
                            throw error;
                        callback(results);
                    });
}

// 10mins-interval checking of table
setInterval(function() {
    dbCheckQueue(function(results) {
        console.log('Fresh meat!')
    });
}, 10*60*1000);

savageguy · Accepted Answer · 2011-11-26 19:16:35Z

3

Ideally you would trigger the event from the where ever the update is being made from after completion.

Since you are using sockets you could create an "Events" connection or similar (authenticate or some other method to indicate it is connecting to trigger an event) which would connect to your socket and trigger an update and disconnect. This could be called from anywhere regardless of platform as long as you are able to create the connection. Once connected the script would send whichever command you want to be triggered and disconnect/finish the updating process.

You could either do this directly from where the update is made or setup a trigger on mysql with sys_exec() to execute another script which does the same as above.

answered Nov 26, 2011 at 19:16

savageguy

1,5933 gold badges13 silver badges18 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Nyxynyx Over a year ago

How can I trigger it from PHP? Maybe just a simple method, because all I need is to send a 1-way message.

savageguy Over a year ago

You would use socket_connect (php.net/manual/en/function.socket-connect.php) and related functions (socket_send) to connect and send your message.

Collectives™ on Stack Overflow

Track Changes to MySQL table using Node.js

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related