Username Availability Check using PHP, AJAX and MySQL

Introduction

This tutorial shows how to check username availability using PHP, AJAX and MySQL. Sometimes you need to check username availability instantly before a user presses the submit button after filling a long-sized signup form. In this case you can use AJAX with any technologies like PHP, CodeIgniter, Servlet, Struts, JSF, Spring, etc. for checking whether the input username is available or already taken by someone else. So if you 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.

Related Posts:

This example uses JavaScript event oninput for checking username availability.

Prerequisites

Apache HTTP Server 2.4, PHP 7.3.5 – 7.4.3, MySQL 8.0.17 – 8.0.22, jQuery Libraries 3.4.1 – 3.5.1

Project Directory

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

Now I 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 I will assume that I am talking with respect to the project’s root directory.

Database Configuration

I 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

I 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 I will create a form where user will input username and check whether the given username is available or not.

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

I 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-3.5.1.min.js" crossorigin="anonymous"></script>
	<!--<script src="https://code.jquery.com/jquery-migrate-3.1.0.min.js"></script>-->
	<script src="https://code.jquery.com/jquery-migrate-3.3.2.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>

MySQL Table

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 COLLATE utf8mb4_unicode_ci NOT NULL AUTO_INCREMENT,
	`login_username` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL,
	`login_password` varchar(255) COLLATE utf8mb4_unicode_ci 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 COLLATE=utf8mb4_unicode_ci;

You 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 php mysql ajax

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 php mysql ajax

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

username availability check php mysql ajax

Source Code

Download

Leave a Reply

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