Auto detect if DB table changes

Question

I have a small application where a users can drag and drop a task in an HTML table.

When user drops the task, I call a javascript function called update_task:

function update_task(user_id, task_id, status_id, text, uiDraggable, el) {
  $.get('task_update.php?user_id='+user_id+'&task_id='+task_id+'&status_id='+status_id+'', function(data) {            
    try {
      jsonResult = JSON.parse(data);
    } catch (e) {
      alert(data);
      return;
    };

In task_update.php I GET my values; user_id, task_id & status_id and execute a PDO UPDATE query, to update my DB. If the query executes correctly, I

echo json_encode ( array (
            'success' => true 
) );

And then I append the task to the correct table cell

if(typeof jsonResult.success != 'undefined') {
  $(uiDraggable).detach().css({top: 0,left: 0}).appendTo(el);
}

This has all worked fine. But, I'm starting to realize, that it's a problem when 2 or more people are making changes at the same time. If I'm testing with 2 browsers, and has the site opened on both for example: Then, if I move a task on browser1, I would have to manually refresh the page at browser2 to see the changes.

So my question is; How can I make my application auto-detech if a change to the DB-table has been made? And how can I update the HTML table, without refreshing the page.

I have looked at some timed intervals for updating pages, but that wouldn't work for me, since I really don't want to force the browser to refresh. A user can for example also create a new task in a lightbox iframe, so it would be incredibly annoying for them, if their browser refreshed while they were trying to create a new task.

So yeah, what would be the best practice for me to use?

Auto-detect and do what with it once it does? You can probably implement MySQL's TRIGGER, if this is what the question is about. — Funk Forty Niner, Nov 16 '14 at 05:52
hmmm... use ajax instead of refresh page and modify tasks accordingly? — bansi, Nov 16 '14 at 05:55
From what I know websockets are the only way to push data from server to client without answering a request. That said you might go for the "interval refresh" and do it with ajax... Although it is a very expensive solution as that would imply a lot of useless requests. — Laurent S., Nov 16 '14 at 06:11
EventSource might be the way to go. [docs](https://developer.mozilla.org/en-US/docs/Web/API/EventSource) — Matt J, Nov 16 '14 at 06:18
You should do [long polling](http://stackoverflow.com/a/12855533/731138) to check for changes live in your application. You can see [here an implementation in php](http://stackoverflow.com/a/25104147/731138). — Alain Tiemblo, Nov 21 '14 at 22:12

score 1 · Answer 1 · answered Nov 16 '14 at 06:47

I would use ajax to check the server at a reasonable interval. What's reasonable depends on your project - it should be often enough that it changes on one end don't mess up what another user is doing.

If you're worried about this being resource intensive you could use APC to save last modified times for everything that's active - that way you don't have to hit the database when you're just checking if anything has changed.

When things have changed then you should use ajax for that as well, and add the changes directly in the page with javascript/jquery.

score 1 · Accepted Answer · answered Nov 19 '14 at 19:37

Use Redis and its publish/subscribe feature to implement a message bus between your PHP app and a lightweight websocket server (Node.js is a good choice for this).

When your PHP modifies the data, it also emits an event in Redis that some data has changed.

When a websocket client connects to the Node.js server, it tells the server what data it would like to monitor, then, as soon as a Redis event was received and the event's data matches the client's monitored data, notify the client over the websocket, which then would refresh the page.

Take a look at this question with answers explaining all of this in detail, includes sample code that you can reuse.

score 0 · Answer 3 · answered Nov 16 '14 at 07:26

If you really need to check a db changes - write a database triggers.

But if nobody, except your code, change it - you can to implement some observation in your code.

Make Observation(EventListener) pattern imlementation, or use one of existed.
Trigger events when anything meaningful happened.
Subscribe to this events

Auto detect if DB table changes

3 Answers3