The Website Design Portfolio & Blog of Ben Tadiar, Plymouth Based Web Designer & Developer.

In my first tutorial I will teach you how to create a username availability checker using jQuery and PHP to query your MySQL database. I found this to be a nice feature while working on a user registration form so thought I would share.

So, first of all we need to create a form. In this example I will keep it simple and only have the username field. We must also create an element for the response to be displayed inside; in this case a span with the id #response.

Username:(Start typing to check availability)

In our stylesheet we need to define 2 styles, unavailable (if the desired username is already taken) and available (if the username is still available). These can be as elaborate as you like, background images can be applied etc, but for the example I have simply changed the font colour.

.unavailable {
color: red;
display:inline-block;
}
.available {
color: green;
display:inline-block;
}

We will use PHP to query our MySQL database and check if the username has already been taken. First we need to create a users table and add a record for testing.

CREATE TABLE IF NOT EXISTS `users` (
  `id` int(8) unsigned NOT NULL auto_increment,
  `username` varchar(40) collate utf8_unicode_ci NOT NULL,
  `password` varchar(40) collate utf8_unicode_ci NOT NULL,
  UNIQUE KEY `id` (`id`)
) ENGINE=MyISAM  DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci AUTO_INCREMENT=2 ;

INSERT INTO `users` (`id`, `username`, `password`) VALUES
(1, 'ben.tadiar', 'password');

Once we have created our table we need to connect to our database, check the if username in the form field already exists, and return the result. This needs to be saved as "availability.php".

<?php
// Database Details
$dbhost = 'localhost'; // Database host
$dbuser = 'root'; // Database user
$dbpass = ''; // Database password
$dbname = 'database'; // Database name
$conn = mysql_connect("$dbhost", "$dbuser", "$dbpass") or die(mysql_error()); // Connect to database server
mysql_select_db($dbname, $conn) or die(mysql_error()); // Select our database
$username = mysql_real_escape_string(htmlspecialchars(strtolower($_POST['username']))); // Escape special characters in string
$query = mysql_query("SELECT username FROM users WHERE username = '$username' LIMIT 1"); // Check if username exists in our database
$check = mysql_num_rows($query); // Check number of rows in result set - In this case should return 0 or 1
echo $check; // Echo the result - 0 is available, 1 is unavailable
?>

Now, we have our form, and we've successfully connected to our database. The last part is to write our jQuery function and post to PHP using AJAX. First off we need to download the jQuery library and include it in our page. Next we need to write the function to check the length of the username and post the data to our form processor.

We're now ready to go. Upload the files to your server and test! I hope this was helpful. You can download the source for this tutorial here.