Use PHP+jQuery AJAX to check MySQL database for changes and load the changes?

Question

I have a database of links that users can submit. Every time a user submits a new link it is added to the database. I have a separate page that lists all the submitted links. How can I have this separate page check the database for changes and load them with AJAX when it finds them?

Answer 1

The page which needs to update depending on database contents must poll the DB every so often. Javascript is a client-side technology and is not designed for directly interacting with back-end servers.

The javascript would look like this (using jquery):

$.post("/webroot/checkForChanges.php", 
        { currentNumber: currNumString },
        function(dat){
            $(dat).find('link').each( function() {
                $('#linksTable').append(""+$(this).text()+"");
            });
});

This will make a POST request to the page you will write (checkForChanges.php) with the variable named currentNumber who has a value which is the string representation of the number of links currently in the table (count these however you like).

the function(dat) part of the code is the callback function that is run when the request completes (which is when the php page has finished processing and the resulting text has been received into the browser).

I will come back to what this is doing when I have explained what the PHP page is doing.

You haven't told us anything about your database structure, but since you are successfully allowing people to add links you must have a sequence defined somewhere which keeps track of an integer id for the link (so that every link can be given a unique id in the database). I'll assume you've called this 'links-count' You should create a simple php page with the following pseudo-code:

//open a database connection
$DB = connect( name, user, password );

//receive value
$currNum = $_POST['currentNumber'];

//check to see if sequence number has incremented since last time:
$seqNum = query( "SELECT currval('links-count')" );

if ($seqNum == $currNum){
    exit(0);
}   //if they are the same, just exit the page without writing anything

//otherwise, carry on... get the result of your query (for new links)
//and loop through, echoing return data

$newEntries = query("SELECT url FROM links WHERE id > ".$currNum);

echo "<newlinks>";

while ( $result = fetch_result( $newEntries ) ) {
    echo "<link><a>".$result."</a></link>";
}

echo "</newlinks>";

The output returned by this page will be an xml document with a node for each link which contains the HTML you want to put in your table cell.

Now we can go back to the callback function in the $.post request in your client-side java code:

function(dat){
    $(dat).find('link').each( function() {
          $('#linksTable').append(""+$(this).text()+"");
    });
});

dat is the returned text, wrap it as a jquery object and then find the collection of tags named 'link'. for each 'link' tag returned by the php page, execute a function which appends a new table row to the linksTable which contains the data inside the link tags.