Use AJAX to watch SQL database for changes.

Question

I have a column in my database that will be updated randomly. Whenever that column is updated I need to refresh content on my page. How can I use AJAX + jQuery to perform an action only on DB changes?

Answer 1

You will have to continually poll a page that has the value from the database.

Answer 2

Hook a special stored procedure into your database that alerts your application after a modification. You'll need a custom module and the right trigger statements.

Your other option is polling.

Answer 3

The server (database/web) can't initiate a connection--only the client can. So you will have to poll the database until there is an update. You can create a web service that checks the database and which jQuery uses.

Edit: I stand corrected. It is possible to keep an AJAX connection open until the server "pushes" data to it. See: http://en.wikipedia.org/wiki/Reverse_Ajax

And apparently it really is polling: http://en.wikipedia.org/wiki/Push_technology#Long_polling. If the server doesn't have any data to send yet, it keeps the connection open until it does. It's not "pure" push technology, because the client doesn't have a listening port which the server connects to. The effect is similar, however.

Edit 2: So back to answering your question... You'll have to choose how to "poll" the web service. The web service would then have to check the database to see if there were updates. Checking the database for updates might be the trickiest and really depends on your requirements. You can run a SQL query to see if anything changed, but how would you know? You would need some sort of parameter (typically a date) to compare against. If done wrong, you could miss some updates or have multiple hits for one update. What Autocracy said would be a good way to get notified of updates. You can keep that list in the database, in memory, etc. and clear it when the client gets the updates.

Answer 4

You will basically have to constantly poll the server for changes to the DB. The server cannot make a call to the client, so the client will just have to constantly ask the server if there have been changes.

Answer 5

I'd suggest creating an HTML page that uses setIntreval() to repeatedly make AJAX calls to a PHP script that queries the your database. You could use JSON and PEAR to make the task a little easier.

Reference Links:

• http://www.w3schools.com/jsref/met_win_setinterval.asp
• http://api.jquery.com/category/ajax/
• http://api.jquery.com/jQuery.getJSON/
• http://php.net/manual/en/function.json-encode.php
• http://pear.php.net/manual/en/package.database.db-dataobject.php

Answer 6

What you are describing is colloquially called Comet programming. Comet describes a group of techniques for pushing content to a web page with a persistent HTTP connection.

The push would be initiated using a trigger/stored procedure combination in the database server. That way, it occurs no matter where the data update comes from.

Answer 7

use setinterval function of javascript for polling and to check the value of the updated dababase field

check following link for more detail http://www.elated.com/articles/javascript-timers-with-settimeout-and-setinterval/

Answer 8

Im doing almost same thing with a chat, reloads a php script every xx sec.

Looks like this: replace j with $ if not using jquery.noconflict..

j(".chatref").everyTime(3000,function(i){

j.ajax({ url: "chatx.php", cache: false, success: function(updated){ j(".chatref").html(updated); ...do stuff.. } });

This is an very nice method i think :) if you want to send vars to chatx.php just add ?php &x=1&y=2?>