JQuery – Username Availability Validation using PHP and MySQL

jQuery Username Availability Validation

It is common operation on each sign up or the registration process, whenever we create or add new user in to the system we needs to add a common identifier for each user and that becomes username, it can be email address or just a string. having said that while doing this process we needs to validate the username and validation is actually check whether particular username is available to user or not.

Let’s make it simple to understand, in programming words check if is there any existing username is present in the database table

In this tutorial I am going to explain each aspect along with the source code. let’s get started step by step:

Step 1: Database Setup

Create MySQL database table to store users details. (if you have already created that’s fine! just match your field name with the source code) and have few dummy records record entries to test.

users table:

CREATE TABLE `users` (
`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,
`username` VARCHAR( 50 ) NOT NULL ,
PRIMARY KEY ( `id` )
) ENGINE = MYISAM ;

CREATE TABLE `users` (

`id` INT( 11 ) NOT NULL AUTO_INCREMENT ,

`username` VARCHAR( 50 ) NOT NULL ,

PRIMARY KEY ( `id` )

) ENGINE = MYISAM ;

SQL Dummy Records:

INSERT INTO `users` (
`id` ,
`username`
)
VALUES (
NULL , 'user1'
), (
NULL , 'user2'
);

INSERT INTO `users` (

`id` ,

`username`

)

VALUES (

NULL , 'user1'

), (

NULL , 'user2'

);

Step 2: Start Programming

Create MySql Database Connection File:

2.1 Create DB connection file db_connection.php and use following code and update the connection variable settings according to your system settings.

<?php

// Connection variables
$host = "localhost"; // MySQL host name eg. localhost
$user = "root"; // MySQL user. eg. root ( if your on localserver)
$password = ""; // MySQL user password (if password is not set for your root user then keep it empty )
$database = "test"; // MySQL Database name

// Connect to MySQL Database
$con = new mysqli($host, $user, $password, $database);

// Check connection
if ($con->connect_error) {
    die("Connection failed: " . $con->connect_error);
}

?>

<?php

// Connection variables

$host = "localhost"; // MySQL host name eg. localhost

$user = "root"; // MySQL user. eg. root ( if your on localserver)

$password = ""; // MySQL user password (if password is not set for your root user then keep it empty )

$database = "test"; // MySQL Database name

// Connect to MySQL Database

$con = new mysqli($host, $user, $password, $database);

// Check connection

if ($con->connect_error) {

die("Connection failed: " . $con->connect_error);

}

Username Availability Check:

2.2 Create PHP file to check username : username_check.php

<?php
if(isset($_POST['username']))
{
	// include Database connection file 
	include("db_connection.php");

	$username = mysqli_real_escape_string($con, $_POST['username']);

	$query = "SELECT username FROM users WHERE username = '$username'";
	if(!$result = mysqli_query($con, $query))
	{
		exit(mysqli_error($con));
	}

	if(mysqli_num_rows($result) > 0)
	{
		// username is already exist 
		echo '<div style="color: red;"> <b>'.$username.'</b> is already in use! </div>';
	}
	else
	{
		// username is avaialable to use.
		echo '<div style="color: green;"> <b>'.$username.'</b> is avaialable! </div>';
	}
}
?>

<?php

if(isset($_POST['username']))

{

// include Database connection file

include("db_connection.php");

$username = mysqli_real_escape_string($con, $_POST['username']);

$query = "SELECT username FROM users WHERE username = '$username'";

if(!$result = mysqli_query($con, $query))

{

exit(mysqli_error($con));

}

if(mysqli_num_rows($result) > 0)

{

// username is already exist

echo '<div style="color: red;"> <b>'.$username.'</b> is already in use! </div>';

}

else

{

// username is avaialable to use.

echo '<div style="color: green;"> <b>'.$username.'</b> is avaialable! </div>';

}

Sample Registration Form to Test Username:

2.3 Create Registration form with HTML and add Jquery and external javascript file (script.js) to head section: index.html

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>iTech Empires Demo - JQuery Username availability validation</title>
<!-- jQuery file -->
<script src="js/jquery-1.11.3.min.js"></script>
<!-- Custom JS file -->
<script src="js/script.js"></script>
</head>
<body>
<h2>JQuery Username availability validation</h2>
Username: <input type="text" id="username" />
<div id="status"></div>
</body>
</html>

<!DOCTYPE html>

<head>

<title>iTech Empires Demo - JQuery Username availability validation</title>

</head>

<body>

<h2>JQuery Username availability validation</h2>

Username: <input type="text" id="username" />

</body>

</html>

2.4 Create and open script.js file and write following code, to check username. : script.js

$(document).ready(function(){
// check change event of the text field
$("#username").keyup(function(){
// get text username text field value
var username = $("#username").val();

// check username name only if length is greater than or equal to 3
if(username.length >= 3)
{
$("#status").html('<img src="loader.gif" /> Checking availability...');
// check username
$.post("username_check.php", {username: username}, function(data, status){
$("#status").html(data);
});
}
});
});