Near Real-Time Updates

For a browser-based application that we produced last year, we found that our end-users were spending a lot of time on the screen - or getting called away before hitting "submit" - and coming back to timed-out sessions.  As users of Google Apps for Government, our folks have gotten used to never having to hit a "save" button and, although we extended the time-out periods, it became something that I wanted to solve before the next release.

The obvious choice was AJAX, but none of us had ever mixed AJAX and CakePHP.  Here's what I worked out to address the issue.  The example that I'll be using is a simple employees table, defined as follows:

CREATE TABLE employees (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    first_name VARCHAR(40) NOT NULL,
    last_name VARCHAR(50) NOT NULL,
    street_address VARCHAR(60),
    city VARCHAR(40),
    state VARCHAR(2),
    zip_code INT,
    department_id INT NOT NULL,
    user_id INT NOT NULL,
    created DATETIME DEFAULT NULL,
    modified DATETIME DEFAULT NULL
);

Note that I have foreign keys to a departments and a users table; the users table is drawn from the CakePHP Authentication and Authorization tutorial, and the departments table is defined as follows (if you wish to include it; it won't be used for this post):

CREATE TABLE departments (
    id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY,
    shortcode varchar(4),
    name varchar(50) NOT NULL,
    created DATETIME DEFAULT NULL,
    modified DATETIME DEFAULT NULL
);

After baking these tables and setting up authentication (the latter is not necessary for this exercise), my first changes were at the template level.  I decided to do the employees update first, as the add requires extra logic and I wanted to put that part off while I worked out the main functionality.

In src/Template/employees/edit.ctp, I added the following code to every keyable form field:

<?= $this->Form->create($employee) ?>
<fieldset>
    <legend><?= __('Edit Employee') ?></legend>
    <?php
        echo $this->Form->input('first_name', array(
            "onblur" => "ajaxUpdate(this.id, this.value, $employee->id)"));
        echo $this->Form->input('last_name', array(
            "onblur" => "ajaxUpdate(this.id, this.value, $employee->id)"));
        echo $this->Form->input('street_address', array(
            "onblur" => "ajaxUpdate(this.id, this.value, $employee->id)"));
...

The JavaScript onblur action fires whenever a form field loses focus, so when the user tabs out of a field or mouse-clicks into another field, the JavaScript function ajaxUpdate will be called.  We're sending three parameters to ajaxUpdate:

• this.id - the HTML "id" of whatever field we're in; e.g. first-name, last-name, etc.
• this.value - the contents of the field.
• $employee->id - the primary key of the row we're editing; CakePHP automatically sets this value in the controller for reference by the views (templates).

Also, at the end of my edit.ctp, I added a status field for my own debugging and feedback purposes:

    <?= $this->Form->end() ?>
    <p>Status: <span id="txtStatus"></span></p>
</div>

In webroot/js, I created a file called ajax.js (I have never been the most imaginative with naming!) which contains the following code function:

function ajaxUpdate(fieldName, fieldValue, rowId) {
     var xmlhttp = new XMLHttpRequest();
     if (fieldValue.length == 0) {
         var param2 = fieldName + "=NULL";
     } else {
         var param2 = fieldName + '=' + fieldValue;
     }
     var param1 = rowId;
     xmlhttp.onreadystatechange = function() {
         if (xmlhttp.readyState == 4 && xmlhttp.status == 200) {
             document.getElementById("txtStatus").innerHTML = xmlhttp.responseText;
         }
     };
     var postURL = "/ajaxcake/employees/ajax/" + param1 + "/" + param2;
     xmlhttp.open("POST", postURL, true);
     xmlhttp.send();
 }

Here we're doing a bit of validation (I am using the word "NULL" when a field is empty) and constructing a CakePHP-usable URL.  My application is storied in the folder "ajaxcake," we're invoking the employees controller, and my method is called "ajax".

On a normal form submit, we'd get the row ID in the URL, and we'd get a request/response object containing the entire row: field names, and all contents whether changed or not.  Here, though, the server is only going to get what we send it, and - because HTTP is stateless - the server won't know what row we're editing or which column needs changing unless we tell it.  That's why we're sending RowID as the first URL parameter and the field we're updating along with its new value (e.g. "first-name=Joe" or "street-address=1234 Main St.") as the second URL parameter.

Our new XMLHttpRequest (xmlhttp) needs a function that will execute on response; that is what is being prepared by xmlhttp.onreadystatechange.  Mostly for debugging purposes, I'm sending status information back to the textStatus to confirm the updates.

If param2 has spaces, that's okay; the browser will fill it in with space codes (%20) and CakePHP will translate them back.

(Credit to W3 Schools for the basis of my AJAX code!)

Okay, so we're almost there.  Now we just need the method in our employees controller that will accept the URL that we've created and called in our Javascript function.  Here is is:

/**
     * Ajax method
     *
     * @param string|null $id Employee ID.
     * @param string|null $field Field Value.
     * @return string|null.
     */
    public function ajax($id = null, $field = null)
    {
        $this->autoRender = false;
        $fieldSplit = explode('=', $field, 2);

        $fieldToUpdate = str_replace('-', '_', $fieldSplit[0]);

$employee = $this->Employees->get($id, [
            'contain' => []
        ]);
        if ($this->request->is(['post', 'get'])) {
            $employee->$fieldToUpdate = $fieldSplit[1];
            if ($this->Employees->save($employee)) {
                echo 'Updated ' . $id . '.' . $fieldToUpdate . ' is now ' . $fieldSplit[1];
            } else {
                echo 'Failed to update ' . $id . '.' . $field;
            }

        } else {
            echo 'Update code bypassed.';
        }
    }

Stepping through, we first turn off autoRender, as CakePHP by default will try to find and render an ajax.ctp at the end of this method, and we don't want to render anything new.  We explode the second parameter so that instead of, for example, "street-address=1234 Main St." we instead get the $fieldSplit array with "street-address" and "1234 Main St.".  Next we replace any dashes that came from the HTML "id" with underscores, to match Cake's database naming conventions.

It's pretty standard CakePHP from here on out; we get the employee by it's id and then we replace the contents of the employee row with the new contents of the field we're updating.  PHP allows for the use of variable variables and that's what we're doing with:

$employee->$fieldToUpdate = $fieldSplit[1];

$fieldToUpdate contains the underscored field name that we're updating, and by expressing it in this context with its dollar-sign, the new data ($fieldSplit[1]) ends up in the $employee object in the appropriate column.

All that's left is the persist the data and then echo a message that will go back to our temporary debugging/status field on the screen (textStatus).  What we set out to achieve has taken place without  the user having pressed a button and with no concentration-disrupting refresh of the screen.

With thousands or tens of thousands of users, I have no idea how this would scale; my organization deals with relatively small numbers.  But in our tests, users tab happily from field-to-field at a normal pace, their data always saved up-to-the-minute... even if they never hit the submit button at the bottom of the form.