Introduction

This tutorial shows how to check username availability using PHP, AJAX and MySQL. Sometimes we need to check username availability instantly before a user presses the submit button after filling a long-sized signup form. In this case we can use AJAX with any technologies like PHP, Codeigniter, Servlet, Struts, JSF, Spring etc. for checking whether the input username is avaialable or already occupied by someone else. So if we give instant result to the user for username availability then sometimes it makes more sensible than while pressing the submit button and goes top of the signup form to rectify the username input field if input username is not available.

If you need the similar example using Codeigniter framework and Servlet please read here Username availability check using Codeigniter, AJAX and MySQL and Username availability check using Servlet, AJAX and MySQL respectively.

This example uses JavaScript event oninput for checking username availability.

Prerequisites

Apache HTTP Server 2.4, PHP 7.3.5, MySQL 8.0.17, jQuery Libraries

Creating Project Directory

It’s assumed that you have setup Apache 2.4, PHP 7.3.5 in Windows system.

Now we will create a project root directory called php-ajax-mysql-username-availability-check under the Apache server’s htdocs folder.

I may not mention the project root directory in subsequent sections and we will assume that we are talking with respect to the project’s root directory.

Database Configuration

We will create a file db.php that will connect to MySQL database server.

<?php
	
	$dbConn = mysqli_connect('localhost', 'root', 'root', 'roytuts') or die('MySQL connect failed. ' . mysqli_connect_error());
	
	function dbQuery($sql) {
		global $dbConn;
		$result = mysqli_query($dbConn, $sql) or die(mysqli_error($dbConn));
		return $result;
	}
	
	function dbNumRows($result) {
		return mysqli_num_rows($result);
	}

	function closeConn() {
		global $dbConn;
		mysqli_close($dbConn);
	}

Username Availability Check

We will create a file process.php that will query database table and check whether the input username is available or someone has already taken.

<?php

require_once 'db.php';

$post_username = $_POST['username'];

if (isset($post_username)) {
	$sql = "SELECT * FROM user WHERE login_username = '" . mysqli_real_escape_string($dbConn, $post_username) . "' LIMIT 1";
	$result = dbQuery($sql);
	
	if (dbNumRows($result) > 0) {
		echo '<span style="color:red;">Username unavailable</span>';
	} else {
		echo '<span style="color:green;">Username available</span>';
	}
	
	closeConn();
} else {
	echo '<span style="color:red;">Username is required field.</span>';
}

Username Availability Checking Form

Now we will create a form where user will input username and check whether the given username is available or not.

We will use here jQuery’s on function with input event to check username live.

We will use jQuery library links directly from the CDN.

<!DOCTYPE html>
<html>
<head>
	<meta charset="UTF-8">
	<title>Check username availability using PHP, AJAX, MySQL</title>
	<script src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
	<script src="https://code.jquery.com/jquery-migrate-3.1.0.min.js"></script>
	<script src="https://code.jquery.com/ui/1.12.1/jquery-ui.min.js"></script>
</head>
<body>
	<div style="margin: 10px 0 0 10px;width: 600px">
		<h3>PHP, AJAX, MySQL - username availability check</h3>
		
		<form id="signupform" style="padding: 10px;">
			<fieldset>
				<legend>Check username</legend>
				<div>
					<label>Username</label><br/>
					<input type="text" name="username" id="username" autocomplete="off"/>
					<div id="msg"></div>
				</div>
			</fieldset>
		</form>
	</div>

	<!-- below jquery code triggered on onblur event and checks the username availability in the database -->
	<script type="text/javascript">
		$(document).ready(function() {
			$("#username").on('input', function(e) {
				$('#msg').hide();
				
				if ($('#username').val() == null || $('#username').val() == "") {
					$('#msg').show();
					$("#msg").html("Username is required field.").css("color", "red");
				} else {
					$.ajax({
						type: "POST",
						url: "process.php",
						data: $('#signupform').serialize(),
						dataType: "html",
						cache: false,
						success: function(msg) {
							$('#msg').show();
							$("#msg").html(msg);
						},
						error: function(jqXHR, textStatus, errorThrown) {
							$('#msg').show();
							$("#msg").html(textStatus + " " + errorThrown);
						}
					});
				}
			});
		});
	</script>
</body>
</html>

Creating Table in MySQL

Create MySQL table called user under database roytuts, which will be used to store the user details information.

CREATE TABLE `user` (
	`user_id` int unsigned NOT NULL AUTO_INCREMENT,
	`login_username` varchar(100) NOT NULL,
	`login_password` varchar(255) NOT NULL,
	`last_login` timestamp NULL DEFAULT CURRENT_TIMESTAMP,
	PRIMARY KEY (`user_id`),
	UNIQUE KEY `login_email_UNIQUE` (`login_username`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

Dumping Data

We need to insert some data in order to complete the test that checks whether the functionality is really working as expected.

insert into `user`(`user_id`,`login_username`,`login_password`,`last_login`) 
values (1,'user1','$2a$08$S5IfrpOVOvFbbOSOmZpjsO5N9PXgEerTloK','2014-07-19 19:18:30'),(2,'user2','$2a$08$v1kJflweCK3FOcoAsmYAUCMxFa5Shh7c2','2013-11-17 19:22:46');

Testing the Application

I assume you have the correct configurations for Apache, PHP and MySQL in Windows Operating System.

Hit the URL http://localhost/php-ajax-mysql-username-availability-check/auth.php to open the home page and you will see below output on the page:

username availability check

If you do not provide any input then you will get input field required error.

If the input username already taken then you will see below message in red color:

username availability check

If username is available then you will see below message in green color:

username availability check

Source Code

You can download source code.

Thanks for reading.

Tags:

2 thoughts on “Username availability check using PHP, AJAX and MySQL

Leave a Reply

Your email address will not be published. Required fields are marked *