PHP DRUPAL: How to detect changes to table in database

Question

Current situation: I have a web page that uses AJAX/JQUERY to refresh all the content on the page every 17 seconds. Every time this happens the server queries the database for data from two tables, one of which is large (450MiB in size, 11 columns) and processes all the data.

This is too resource intensive. I want:

1. The server queries the database only when one of the two tables have changed.
2. The page then reloads the page through AJAX only when the tables have been updated and the server has re-processed the data.

I think this falls under the category of comet programming. I'm not sure.

2 is easy. The webpage calls 'update.php' every 17 (or maybe less) seconds. The PHP script returns no data if no changes have been made. Only if data is returned then the current page is replaced with the new data. Please advise me if there is a better way.

As for 1 my googling tells that every time one of my two tables is updated I can put a notification in a table (or maybe just a single byte in a file) to indicate that I must query the database again and then the next time that the webpage sends an AJAX request I return the data.

The problem is that I'm working with a rather large code base I didn't write and I don't know of all the places that either of the two tables may be updated. Is there an easier way to check when the database is modified.

I'm working with PHP, Apache, Drupal and MYSQL.

Answer 1

You can chekout Server Sent Events

A server-sent event is when a web page automatically gets updates from a server.

there is an excellent article on HTML5rocks.com - Server Sent Events

All You have to do is create an object

var source = new EventSource('xyz.php'); //Your php files which will return the updates.

Once you create an object,you can listen to the events

source.addEventListener('message', function(e) {
  console.log(e.data);
}, false);