Notifying users of database changes

Question

I have, at its heart, a php/mysql application. Every ~15 minutes new data is added to the database. I want to notify users that new data is in the database. I was thinking of having some JS run on the clients computer to check for updates every 60 seconds, but I don't want to pull down the results unless something has actually changed/been added in the DB.

So my question is: using PHP/JS/MYSQL what is the best way for clients to detect new rows in the DB?

One possible solution I was kicking around was having a seperate table called "switch" with a BOOL called 'switch', and everytime I update the DB setting "switch" to true for 60 seconds. Then the client computers check to see if switch.switch = '1' (true) every 60 seconds. Thoughts? I'm open to suggestions on any part of this plan? I do have the jQuery library loaded if anyone wants to make a jQuery suggestion...

Answer 1

Instead of your "switch" just being a flag, have it be a version number. Increment the number whenever you put new stuff in the database. Then the clients just compare their number with the one they fetch periodically, and when the numbers are different they know it's time for an update. (Obviously they remember the new number for the next test ...)

Answer 2

I would consider using a timestamp or a date/time field vs. a boolean value in your table. The client could compare the last time they checked (preferable using the servers time) with what is in the table. If new data has been loaded based on the tables date/time being newer than what the client provided then send back the updated data.

Answer 3

I like your one row table, but would have a time stamp of the lastupdate, only to be updated after the batch update occurs.

Is it a problem if clients are reading data during an update?

Answer 4

Can you maintain a flag in PHP and have your clients submit every 60 min and check the flag?

Answer 5

An update/version number (either the time of the update or an increasing integer) is the way to go. I do this in my app.

Some tips:

1 Use memcache instead of storing the data in the dbms. Lowers the load on the dbms, and faster response to the queries.

2 Is there only one set of data that is updated every 15 minutes? If so, then the cache key could be a simple "data_ver" If there are multiple sets of data, each with its own update cycle, then include the data_set number as part of the key. Eg data__ver or data_1_ver, data_2_ver, etc

3 Rather than having the client guess at whether the data load has completed or not (you suggested waiting 60 sec in a comment), it would be much better for the data load process to indicate that it has completed. -- And when it is completed, the version would be updated.

4 An advantage of saving a version date/time rather than a simple number is that it enables you to include a status message to the user of "Data updated 1 Jan 2010 11:12 AM EST" Remember to deal with timezone issues--your clients may NOT be in the same timezone as the server. They could be East or West of the server.

5 If you want to wait on the client for things to "settle down" after a change, then you can start a local timer on the browser. You do not need to worry about synchronizing clocks between the browser and the server. Just wait 60 seconds after the update number changes before downloading the new data.

6 If you have a lot of clients and the data download is of good size, then you should think about including a random delay of 0-90 sec or so on the client after the version changes and before requesting the download. Otherwise you can end up with all of the clients trying to download the updated data all at once.

Answer 6

if you can set a timestamp of last update for each user, and have a trigger so that when all the changes corresponding to that use are complete changes their timestamp. Then you can use the jQuery to check if that their current timestand matches the timestamp of last update for their user.

Answer 7

I recommend using MySQL triggers in AFTER mode to insert an entry into a separate table with the version ID auto-increment or similar. The last version ID can be stored as URL/Cookie or whatever mechanism you are employing and compared against the latest version generated by the trigger.