• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 298
  • Last Modified:

Update record set every time database is updated

Hi all,

Is it possible to have a a recordset driven from PHP & MySQL update automatically without a page refresh when new data is entered into the database?

So the list may be ordered by any field ASC or DESC and displayed on a page, and when new data is entered it will insert the new data into the displayed list in the correct order.  

I'm thinking AJAX, JQuery, obviously PHP and MySQL, but not quite sure where to start?
Want to keep it low server/page/memory intensive.

Anyone tried to achieve this before?

Thanks
0
dev09
Asked:
dev09
  • 2
2 Solutions
 
Hugh McCurdyCommented:
Your load will depend on several factors including how often you check the DB record to see if it changed.

Yes, I have tried something like this before.  I used AJAX, PHP and Javascript.

It is hard for me to tell you where to start since I don't know much about your background or what you've done so far.  You could start with reading about AJAX.  

http://www.w3schools.com/ajax/default.asp

Caveat - I don't know if AJAX is the best approach.  I can only say that I've made AJAX work.  I've heard that Java could work too but, as of yet, I haven't tried that approach.  You might want to wait a while to see if others come up with better ideas than AJAX.

Oh, you might want to read this --  http://en.wikipedia.org/wiki/Push_technology   I don't know if it's interesting to you or not.
0
 
Ray PaseurCommented:
AJAX is the right answer.  jQuery is a good tool for initiating repeated AJAX requests to the background script.  You can run the background script once a second without imposing any meaningful load on the server if your data base tables are correctly indexed.  For most clients this will appear to be contemporaneous synchronization between the data base and the browser output.

Anyone tried to achieve this before?  Yep, that is exactly how Gmail works for millions of users every day.

The general design will be to send a JavaScript string to the client machine.  The JavaScript will use a timer (JS timers are given in thousandths of a second) to initiate the AJAX requests to the background script.  The background script will run the query and return a string of information that will get put into a <div>.

See if this code snippet makes sense to you.  I don't remember where I found it, but believe that it was written by Rasmus Lerdorf, of PHP fame.

Good luck with your project, ~Ray
I find a lot of this AJAX stuff a bit of a hype.  Lots of people have
been using similar things long before it became "AJAX".  And it really
isn't as complicated as a lot of people make it out to be.  Here is a
simple example from one of my apps.  

/* THE JAVASCRIPT TO CREATE THE AJAX INFRASTRUCTURE */
/* FUNCTION TO CREATE THE BROWSER-DEPENDENT REQUEST OBJECT */
function createRequestObject() 
{
    var ro;
    var browser = navigator.appName;
    if(browser == "Microsoft Internet Explorer")
    {
        ro = new ActiveXObject("Microsoft.XMLHTTP");
    }else
    {
        ro = new XMLHttpRequest();
    }
    return ro;
}


/* FUNCTION TO SEND THE REQUEST */
function sendReq(action) 
{
    http.open('get', 'ajaxprocessor.php?action='+action);
    http.onreadystatechange = handleResponse;
    http.send(null);
}


/* FUNCTION TO HANDLE THE RESPONSE */
function handleResponse()
{
    if(http.readyState == 4)
    {
        var response = http.responseText;
        var update = new Array();

        if(response.indexOf('|' != -1))
        {
            update = response.split('|');
            document.getElementById(update[0]).innerHTML = update[1];
        }
    }
}


/* CREATE THE OBJECT */
var http = createRequestObject();

/* END OF THE JAVASCRIPT */

This creates a request object along with a send request and handle
response function.  So to actually use it, you could include this js in
your page.  Then to make one of these backend requests you would tie it
to something.  Like an onclick event or a straight href like this:

<a href="javascript:sendReq('foo')">[Foo]</a>

That means that when someone clicks on that link what actually happens
is that a backend request to ajaxprocessor.php?action=foo will be sent.

In ajaxprocessor.php you might have something like this:

/* THE PHP TO HANDLE THE REQUEST FROM THE FRONT-END SCRIPT */
switch($_GET['action']) 
{
    case 'foo':
      // DO WHATEVER PROCESSING IS APPROPRIATE
      // THEN RETURN A RESPONSE STRING TO handleResponse()
      echo "foo|Foo done";
      break;
      // ETC...
}

Now, look at handleResponse.  It parses the "foo|Foo done" string and
splits it on the '|' and uses whatever is before the '|' as the dom
element id in your page and the part after as the new innerHTML of that
element.  That means if you have a div tag like this in your page:

<div id="foo">Waiting for Foo</div>

Once you click on that link, that will dynamically be changed to:

<div id="foo">Foo done</div>

That's all there is to it.  Everything else is just building on top of
this.  Replacing my simple response "id|text" syntax with a richer XML
format and making the request much more complicated as well.  Before you
blindly install large "AJAX" libraries, have a go at rolling your own
functionality so you know exactly how it works and you only make it as
complicated as you need.  Often you do not need much more than what I
have shown here.

Expanding this approach a bit to send multiple parameters in the
request, for example, would be really simple.  Something like:

function sendReqArg(action,arg) 
{
    http.open('get', 'ajaxprocessor.php?action='+action+'&arg='+arg);
    http.onreadystatechange = handleResponse;
    http.send(null);
}

And your handleResponse can easily be expanded to do more interesting 
things than just replacing the contents of a div.

-Rasmus (Lerdorf?)

Open in new window

0
 
Hugh McCurdyCommented:
Ray, thanks for the good post.  As usual, I learn more just by hanging around.

dev, my comment on load was that you can drag down the server if you keep querying for changes every 100ms.  Or if there are a "million" requests each "second."  (Although a "million" requests has to be solved some other way, with more hardware.)

Good luck with your project.  If you get stuck, people are here to help.
0
 
dev09Author Commented:
Thanks guys, i've not quite put this into a working model but you've given me the right path to follow down!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now