How to check username/email availability from database

In this tutorial, i will explain you about the functionality all the websites implement for their users.
Checking for the availability of an username or email in database.
Checking an email/username helps web application to find duplicate insertions and it also helps to prevent duplicate content and users.
If username and email already exists in the database then we show custom message and if not we will insert the data on database.

Step 1: Create database and insert the values.

CREATE TABLE IF NOT EXISTS `email_availabilty` (
`id` int(11) NOT NULL,
  `email` varchar(255) NOT NULL,
  `username` varchar(255) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=latin1;


// Insert the values in to sql
INSERT INTO `email_availabilty` (`id`, `email`, `username`) VALUES
(2, 'demo@gmail.com', 'demo'),
(3, 'anu@gmail.com', 'anuj'),
(4, 'codenap@gmail.com', 'codenap');

--
-- Indexes for table `email_availabilty`
--
ALTER TABLE `email_availabilty`
 ADD PRIMARY KEY (`id`);

--
-- AUTO_INCREMENT for table `email_availabilty`
--
ALTER TABLE `email_availabilty`
MODIFY `id` int(11) NOT NULL AUTO_INCREMENT,AUTO_INCREMENT=5;

Step 2: Create a config.php file in your localhost server and write the connection of mysql and php.

$con = mysqli_connect("localhost","root","","demos");

// Check connection
if (mysqli_connect_errno())
  {
  echo "Failed to connect to MySQL: " . mysqli_connect_error();
  }

Step 3:

Create index.php file and in that file add the following code where it contains forms and javascript which connects to database and check in the database for the values.

<?php
include_once("config.php");


 ?>
<!DOCTYPE html>
<html lang="en">
	<head>
		<meta http-equiv="content-type" content="text/html; charset=UTF-8">
		<meta charset="utf-8">
		<title>CodeNap DEMO</title>
		<meta name="generator" content="Bootply" />
		<meta name="viewport" content="width=device-width, initial-scale=1, maximum-scale=1">
		<link href="css/bootstrap.min.css" rel="stylesheet">
		<!--[if lt IE 9]>
			<script src="//html5shim.googlecode.com/svn/trunk/html5.js"></script>
		<![endif]-->
		<link href="css/styles.css" rel="stylesheet">
		<script>
function checkemailAvailability() {
$("#loaderIcon").show();
jQuery.ajax({
url: "check_availability.php",
data:'emailid='+$("#emailid").val(),
type: "POST",
success:function(data){
$("#email-availability-status").html(data);
$("#loaderIcon").hide();
},
error:function (){}
});
}

function checkusernameAvailability() {
$("#loaderIcon").show();
jQuery.ajax({
url: "check_availability.php",
data:'username='+$("#username").val(),
type: "POST",
success:function(data){
$("#username-availability-status").html(data);
$("#loaderIcon").hide();
},
error:function (){}
});
}
</script>
	</head>
	<body>
<nav class="navbar navbar-default navbar-fixed-top" role="navigation">
	<div class="navbar-header">
        <a class="navbar-brand" rel="home" href="">CodeNap Blog</a>
		<button type="button" class="navbar-toggle" data-toggle="collapse" data-target=".navbar-collapse">
		<span class="sr-only">Toggle navigation</span>
		<span class="icon-bar"></span>
		<span class="icon-bar"></span>
		<span class="icon-bar"></span>
		</button>
	</div>

</nav>


     
 <!--/left-->
  
  <!--center-->
  <div class="col-sm-7">
    <div class="row">
      <div class="col-xs-12">
        <h3>Check username and email With AJAX(JQuery) and PHP </h3>
		<hr >
		<form name="insert" action="" method="post">
     <table width="100%"  border="0">
	 <tr>
	 <td height="41" colspan="2">
	<b>NOTE:</b> Please type an email and continue filling the other fields. You'll see the validator in action.</td>
	 </tr>
	  <tr>
	 <td height="41" colspan="2">
 Already existing email in this demo: <b>demo@gmail.com, anu@gmail.com, codenap@gmail.com</b></td>
	 </tr>
	  <tr>
	 <td height="41" colspan="2">
 Already existing username in this demo: <b>demo, anu, john</b></td>
	 </tr>
	
  <tr>
    <th width="24%"  height="46" scope="row">Email Id :</th>
    <td width="71%" ><input type="email" name="email" id="emailid" onBlur="checkemailAvailability()" value="" class="form-control" required /></td>
</tr>
<tr>
  <th width="24%"  scope="row"></th>
<td >   <span id="email-availability-status"></span> </td>
  </tr>
  <tr>
    <th height="42" scope="row">User Name</th>
    <td><input type="text" name="username" id="username" value="" onBlur="checkusernameAvailability()" class="form-control" required /></td>

  </tr>
  <tr>
  <th width="24%"  scope="row"></th>
<td >   <span id="username-availability-status"></span> </td>
  </tr>
</table>



     </form>
 
      </div>
    </div>
    <hr>
        
   
  </div><!--/center-->


		<script src="//ajax.googleapis.com/ajax/libs/jquery/2.0.2/jquery.min.js"></script>
		<script src="js/bootstrap.min.js"></script>
	</body>
</html>

The function checkemailAvailability() in the head script is used to check the email address availability from the database by posting the variable to ajax and display the appropriate message accordingly

checkusernameAvailability() function is for checking if the username exits in the database by mysql and ajax.

Step 4:

Crete check_availability.php file for checking the values from post variables.

<?php 
require_once("config.php");
//code check email
if(!empty($_POST["emailid"])) {
$result = mysqli_query($con,"SELECT count(*) FROM email_availabilty WHERE email='" . $_POST["emailid"] . "'");
$row = mysqli_fetch_row($result);
$email_count = $row[0];
if($email_count>0) echo "<span style='color:red'> Email Already Exit .</span>";
else echo "<span style='color:green'> Email Available.</span>";
}
// End code check email

//Code check user name
if(!empty($_POST["username"])) {
	$result1 = mysqli_query($con,"SELECT count(*) FROM email_availabilty WHERE username='" . $_POST["username"] . "'");
	$row1 = mysqli_fetch_row($result1);
	$user_count = $row1[0];
	if($user_count>0) echo "<span style='color:red'> Username already exit .</span>";
	else echo "<span style='color:green'> Username Available.</span>";
}
// End code check username
?>

STpe 5 :

Thats it. You have created a script for checking username/email exists in database.

Demo Download

Leave a Reply