Rob Wiltbank

Educator. Technologist. Web Developer.

Google +PinterestLinkedinTwitterRSS
  • Home
  • Publications & Research
  • Resume
  • About Rob
  • Contact Rob

Asynchronous Page Refresh with JQuery and PHP

Posted in: JQuery, Oracle, PHP, Web Development September 29th, 2014

Tweet about this on Twitter
Twitter
Pin on Pinterest
Pinterest
Share on Facebook
Facebook
Share on Reddit
Reddit
Email this to someone
email
Electronic Status Board

Electronic Status Board

You know those “In/Out” boards in the workplace?  In an attempt to bring our office into the ’00’s, I was tasked with writing an electronic version in the form of a web application.

My general platform is rooted in Linux, Apache, PHP and Oracle on the server-side utilizing Bootstrap for the front-end user interface.

Understanding that this was to be a very simple application, I took a very straight forward approach by creating a single table in Oracle to track the username; whether they are in, out, at lunch or in a meeting; a short note; their ‘mood’; and, a Unix timestamp.

There’s one row per user that just gets updated with whatever their current status is.  I wrote a simple page that displays the contents of the table and allows for the user to click on their name to change their status, doing note and mood  by way of a modal screen in Bootstrap — for bells and whistles, I dynamically pull their photograph out of our ID Badge database and resize it on the fly, but that’s another blog post…  my concern rested in how to update everyone’s page in a reasonable amount of time without doing a full page refresh every 10 seconds.

Simple Status Board Database Schema

Simple Status Board Database Schema

To keep everything as straight forward as possible, my psuedo-process was this:

  1. When the page first loads, get the MAX(WSTATUS_TIMESTAMP) from the table and store it in the PHP superglobal $_SESSION[‘MAXTS’].
  2. Create a simple AJAX mechanism that asyncronously polls a PHP page every 10 seconds and pulls the MAX(WSTATUS_TIMESTAMP) from the table.
  3. Return a 1 or 0 to the function to determine if we need to do a page refresh.

On my status page, I used the following javascript that, in the background, sends a request out to a page called checkRefresh.php which returns a 1 if a refresh is needed or a 0 if a refresh isn’t needed:

$(document).ready(function()
{
    var refreshID = setInterval( function() {
        $.ajax({
            type: 'GET',
            url: 'checkRefresh.php',
            dataType: 'html',
            success: function(html, textStatus) {
                //Handle the return data (1 for refresh, 0 for no refresh)
                if(html == true)
                {
                    location.reload();
                }
            },
            error: function(xhr, textStatus, errorThrown) {
                alert(errorThrown?errorThrown:xhr.status);
            }
        });
    }, (1000 * 10)); //Poll every 10 seconds.
});

Here’s the code for the PHP page:

<?php
//Include my session file which has session_start() and my database credentials.
include('include/session.php');

//Get the latest maximum timestamp.
$sql = "SELECT MAX(WSTATUS_TIMESTAMP) AS MAXTS
        FROM WSTATUS";
$sid = oci_parse($oracle, $sql);
oci_execute($sid);
$row = oci_fetch_assoc($sid);

//Compare the latest database timestamp with the one in the session.
if($row['MAXTS'] > $_SESSION['MAXTS'])
{
    //If there's something more recent, store it and return true.
    $_SESSION['MAXTS'] = $row['MAXTS'];
    echo 1;
}
else
{
    //Otherwise, we have no reason to refresh.  Return false.
    echo 0;
}
?>

So, to recap, we store the newest entry (MAX() timestamp) in the database on page load. Then, every 10 seconds, we poll the database to see if someone has updated their status. If so, we send back a true and we use a simple location.reload() to refresh the page.

While MySQL has a built-in function for Unix timestamps, Oracle doesn’t natively handle that.  You can convert Oracle’s SYSDATE to a Unix timestamp using the following query as an example:

SELECT (SYSDATE – TO_DATE(’01-JAN-1970′,’DD-MON-YYYY’)) * (86400) FROM DUAL;

The one major caveat of this application as a whole is, if you have a lot of users frequently updating their status, you may get to a point where the page may refresh in the middle of a user updating their status. In this event, you may want to extend the if() condition that fires off the refresh to wait if the modal screen is open.

Tweet about this on Twitter
Twitter
Pin on Pinterest
Pinterest
Share on Facebook
Facebook
Share on Reddit
Reddit
Email this to someone
email

Tags: ajax, jquery, oracle, php, Web Development

Leave a Comment Cancel

You must be logged in to post a comment.

Blog Post Categories

  • General (1)
  • Instructional Technology (1)
  • JQuery (1)
  • Oracle (1)
  • Pedagogy (2)
  • PHP (3)
  • Strategies (1)
  • Teaching (1)
  • Web Development (3)

Keyword Cloud

ajax cli command line facebook google+ image jquery lesson planning manipulation oracle pedagogy php social media teaching technology tpack twitter Web Development

Follow Pinterest's board Pin pets on Pinterest.
artificial grass for putting greens