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.

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.