fetching data from mysql with jquery

I want to fetch some data from mySQL without refreshing the page using jQuery. Can someone please tell me how I can fetch the data if any record in mysql table is updated. For example, following code fetches a number count from the 开发者_Python百科database, if someone add a number in the database and it is updated, how I can display the new number without page refresh? Thanks.

<?
$query  = "SELECT number, name FROM members WHERE id=1";
$result = mysql_query($query);

$row = mysql_fetch_array($result);
$number = $row['number'];    ?>

It's been mentioned two times already, but I still want to underline it. You would never want a client to be able to access the database directly. It is a serious security risk.

Now to the solution. First of all you will want to set up a PHP-file that you can request with ajax. Let's call this check.php and it will look something like this:

<?php
// include necessary files to enable connection to the database
$query  = "SELECT number, name FROM members WHERE id=1";
$result = mysql_query($query);

$row = mysql_fetch_array($result);
$number = $row['number'];

// send correct content type header of json
header("Content-Type", "application/json");

// we create an array, and encode it to json
// then echo it out while killing the script
die(json_encode(array('numbers'=>$number)));

And now onto the JavaScript. The solution is similar to that of Kyle Humfeld, but will not be using the setInterval, because it's a really bad practice. The reason behind this is because setInterval will not care about the status of your ajax call, if it has finished or not. So you might end up with stacking requests if there's something wrong with the server, and this is not good.

So to prevent this, we instead use a combination of the success-callback of the ajax method (.getJSON is essentially a shorthand for .ajax) and setTimeout to create something that's called polling:

$(function(){
    // placeholder for request
    var request = null;

    // request function
    var check = function() {
        request = $.getJSON('check.php', function(data){
            // this will log the numbers variable to the dev console
            console.log(data.numbers);
            // initiate a timeout so the same request is done again in 5 seconds
            setTimeout(check, 5000);
        });
    });

    // initiate the request loop
    check();

    // if you want to cancel the request, just do request.abort();
});

Also, there's a more advanced solution using a comet server that will push data from the server to the client, but you should try to get the above to work first before digging into comet. If you want to read up on the subject I suggest that you take a look at APE.

As @Pekka mentions, you'll want to make an AJAX call to a PHP page that does the db call. However, it looks like your problem is a little more complicated than that, since you want to check periodically and update (part of?) your page when a new record is inserted, right?

If so, you should be able to use setInterval() to do that. It's a native javascript function, and basically just sets up an interval (in miliseconds) after which to run some code. The thing that makes setInterval() different than setTimeout() is that setInterval() runs repetitively until you tell it to stop (using clearInterval()).

So you can use this to make an AJAX call every 30 seconds (or whatever you prefer) to look for new records and update a results div accordingly.