There are few Web-related tasks more annoying than registering for a new website account. Providing yet another online entity with various bits of personally identifiable information (did you know that in many cases you can be personally identified simply by providing your age, gender and zip code?) and frustrations surrounding a lengthy and often clumsy registration process are just two of the several aggravations which can arise when creating a new account.
In recognition of the widespread reluctance to manage multiple accounts, several decentralized authentication solutions have gained traction, including notably OpenID and Facebook, however such approaches also have problems. So in spite of the availability of these third-party authentication solutions many website developers still choose to go it alone and manage user accounts locally. If you fall into this latter grouping, it is incumbent upon you to take every step possible to streamline the registration process in order to reduce the risk of driving off frustrated users (many of whom will hopefully eventually become customers).
One of the easiest ways to streamline the registration process is by providing the user with real-time feedback regarding username availability. This is accomplished by monitoring the registration form's username field and immediately following the user's completion of this field, rather than waiting for the user to complete all fields and submit the form. Although a seemingly complex feature, it's actually easily accomplished thanks to the powerful jQuery library. In this article I'll show you to how create a username existence validator using jQuery, PHP and MySQL.

The MySQL Accounts Table

The MySQL table used to manage the user accounts (for the purpose of this example) is straightforward, consisting of an integer-based primary key, unique username field, and a hashed password field:
CREATE TABLE accounts (
  id INTEGER UNSIGNED NOT NULL AUTO_INCREMENT PRIMARY KEY,
  username VARCHAR(255) NOT NULL UNIQUE,
  password CHAR(32) NOT NULL
);
The username field's UNIQUE constraint ensures two identical usernames aren't stored in the database, which fulfills the important goal of data consistency. However we also want to provide registering users with real-time feedback regarding whether his username is available, necessitating client communication with the database by way of the PHP-based intermediary script.

jQuery's blur Handler

The jQuery library supports multiple ways to trigger an event based on user actions, including when a form field loses focus as a result of the user tabbing or using the mouse to move to the next field. This event is known as a blur. As an example, suppose the registration form looks like this:
<form action="register.php" method="post">

  <p>
    Choose a username:<br />
    <input type="text" id="username" name="username" size="25" />
  </p>

  <p>
    Choose a password:<br />
    <input type="password" id="password" name="password" size="25" />
  </p>

  <p>
    <input type="submit" name="submit" value="Create Your Account" />
  </p>

</form>
We can use jQuery's blur() method to fire when focus is removed from the username field, as demonstrated here:
<script src="http://www.google.com/jsapi"></script>
<script type="text/javascript" >
  google.load("jquery", "1");
</script>

<script type="text/javascript" charset="utf-8">
google.setOnLoadCallback(function() {
  $('#username').blur(function() {
    alert('You just left the username field.');
  });
});
</script>
We'll use the blur() method in conjunction with jQuery's Ajax capabilities to talk to a PHP script.

Talking to PHP with jQuery

JQuery supports a method called $.post which facilitates the transmission of form values to the Web server. We can use the $.post method to send the desired username to a PHP script, which will subsequently query the accounts table to determine whether the username has already been chosen by another user. Let's start with the revised jQuery code:
<script type="text/javascript" charset="utf-8">
google.setOnLoadCallback(function() {
  $('#username').blur(function() {
    $.post('validate.php', {'username': $('#username').val()},
      function(response) {
        if (response == 0) {
          $("#exists").html("Available!");
        } else {
          $("#exists").html("Not available!");
        }
      }
    );
  });
});
</script>
The $.post() method sends a POST request to validate.php (which in this example resides in the same directory as the registration form; you'll use a URL if the validator resides elsewhere), passing along a POST variable named username which contains the current value of the username field. The returned response value is examined to determine whether the username is available, and a DIV named exists is updated accordingly. Therefore you'll need to add the DIV to the form, presumably in the immediate vicinity of the username field:
<input type="text" id="username" name="username" size="25" />
<span id="exists"></span>
The PHP script (validate.php ) used to determine whether the username exists is presented next. As you can see this is a straightforward script, using the MySQLi extension in conjunction with prepared statements to determine whether the provided username exists. If no rows are found, zero is returned, otherwise one is returned.
<?php

  // jQuery's $.post() sends parameters using the POST method
  $username = $_POST['username'];

  // Connect to the MySQL server
  $db = new mysqli("localhost", "webuser", "secret", "website");

  // Create a prepared statement which queries the table for a username
  $stmt = $db->prepare("SELECT id FROM accounts WHERE username = ?");

  // Bind the $username variable to the placeholder
  $stmt->bind_param('s', $username); 

  // Execute the prepared statement
  $stmt->execute();

  // You *must* store the result in order to determine the row count
  $stmt->store_result();

  // How many rows were returned?
  echo $stmt->num_rows;

?>
The beauty of this approach is the ability to decorate the response in any way you see fit. While this example simply updated a DIV with a simple message, you could use jQuery's other DOM manipulation capabilities to add or change the DIV's CSS attributes, cause the form field to turn red or green depending on the outcome, or implement any other visual cue which you think will help the user to effortlessly complete the registration process.

Conclusion

Do you use Ajax to simplify the account registration process? Share your tips and tricks in the comments!

About the Author

Jason Gilmore is founder of the publishing, training, and consulting firm WJGilmore.com. He is the author of several popular books, including "Easy PHP Websites with the Zend Framework", "Easy PayPal with PHP", and "Beginning PHP and MySQL, Fourth Edition". Follow him on Twitter at @wjgilmore.