Solved

Update record set every time database is updated

Posted on 2011-09-29
4
285 Views
Last Modified: 2012-05-12
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
Comment
Question by:dev09
  • 2
4 Comments
 
LVL 13

Accepted Solution

by:
Hugh McCurdy earned 250 total points
ID: 36818941
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
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 36891521
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
 
LVL 13

Expert Comment

by:Hugh McCurdy
ID: 36892047
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
 

Author Closing Comment

by:dev09
ID: 37055025
Thanks guys, i've not quite put this into a working model but you've given me the right path to follow down!
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
while loop in html mail format 5 32
Help cleaning out CSS 2 26
Can't connect to my database 2 13
Datepicker in PHP 8 0
Developers of all skill levels should learn to use current best practices when developing websites. However many developers, new and old, fall into the trap of using deprecated features because this is what so many tutorials and books tell them to u…
Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
Learn the basics of lists in Python. Lists, as their name suggests, are a means for ordering and storing values. : Lists are declared using brackets; for example: t = [1, 2, 3]: Lists may contain a mix of data types; for example: t = ['string', 1, T…
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now