How to check if email already exists in database using Ajax in PHP

During user registration or signup, an email id or user id is the unique id we usually use to identify the user. So, we must check that the same email id or user id does not already exist in the database for another user during registration. If exists, we should give a message to the user. Using Ajax in PHP, we can validate duplicate emails and show the message before submitting the form.

In this topic, we will develop a small registration form where users can enter details along with the email id to register. We will show how you can write Ajax code to verify if the email id is already registered and give a message accordingly before the user submits the form.

Assumption

You must have basic knowledge of PHP, HTML, CSS and JavaScript/jQuery.
You must have basic understanding of Ajax. You can read below topics for more details:

Folders and Files

Below is a screenshot of the folder structure and files I am using:

check if username already exists in database using Ajax

I have created a folder named 'registration' under 'xampp/htdocs' as I am using XAMPP. If you are using WAMP, create 'registration' folder under 'www'.

Folder 'cfg' - In this folder I have kept dbconnect.php which is used to connect to the MySQL database.
Folder 'css' - My custom stylesheet is in this folder
Folder 'js' - In this folder I have my custom JavaScripts file
index.php is my main program for registration. User can enter data and submit.
check_email.php is the PHP program call from Ajax. This php program checks if same email id exists in the database table.
process_registration.php is the php code written to process the registration. This program works after form is submitted.

Below is the screenshot of index.php

check if email already exists in database in SQL Server

In the above screen, you can see a form is displayed, we will validate email id once it is entered.

Development Steps

Step 1 - Create a MySQL table for user registration
Step 2 - Connect to MySQL database (dbconnect.php)
Step 3 - Create the registration form (index.php)
Step 4 - Write Ajax script for email validation (validate.js)
Step 5 - Process registration form after Submit (process_registration.php)
Step 6 - Add CSS (style.css)

Step 1 - Create a MySQL table for user registration

Let us create a table named 'candidate' in MySQL database. This table will have candidate details, like name, email and gender etc. I have a database called 'demo'. Table structure is given below:

check if mobile number already exists in database using Ajax

Table has 5 columns in it.

candidate_id - It is the primary key and auto incremented id of the candidate
name - Name of the candidate
email_id - Email Id of the candidate
registration_date - Date of submission
gender - Gender of the candidate. Enum type with valiues Male, Female and Other.

Create table script for this table is given below.

candidate.sql


CREATE TABLE `candidate` (
  `candidate_id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `email_id` varchar(200) NOT NULL,
  `registration_dt` date NOT NULL,
  `gender` enum('Male','Female','Other','') NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `candidate`
  ADD PRIMARY KEY (`candidate_id`);

ALTER TABLE `candidate`
  MODIFY `candidate_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

After you create the table, please verify if table structure is same as above. I am not inserting any data in the table initially. By submitting the form, data will be inserted in this table.

Step 2 - Connect to MySQL database (dbconnect.php)

We will connect to MySQL database using PHP scripts. We will create a separate php program for this purpose and include it in other programs.

dbconnect.php


<?php
  $server="localhost";
  $userid="root";
  $pwd="";
  $dbname="demo";
  $conn = mysqli_connect($server, $userid, $pwd, $dbname);
//Check connection
if (!$conn) 
  	die("Connection Error: " . mysqli_connect_error());
?>

I am using mysqli_connect() function with 4 parameters.

server - localhost
userid - we are using root user
password - no password for user root
database name - we are using "demo" database.

If connection is successful then it will return true and false otherwise. You can read the topic How to connect to MySQL database in PHP using MySQLi and PDO for more details on database connection using PHP.

Step 3 - Create the registration form (index.php)

We will develop a registration form with 3 input fields - name, email_id and gender. We are just using these three fields to show how email_id can be verified from database if it is already registered. It is more to show you can check email id while entering data in the form and give message to the user, rather than how you create a registration form and submit it.

Let's see the code.

index.php


<!DOCTYPE html>
<html lang="en">
<head>
  <title>Online Registration</title>
  <meta charset="utf-8">
  <meta name = "viewport" content = "width=device-width, initial-scale=1">
  <link rel = "stylesheet" href = "https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
  <link  rel = "stylesheet" href = "css/style.css">
</head>
<body>
  <?php include "process_registration.php";?>
  <div class="container">
  <h4>Candidate Registration</h4>
  <form id ="frm" action = "index.php" method = "post">
    <p id="msg" style="text-align: center;"><?php echo $msg;?></p>
      <div class="form-group col-md-12">
    	 	<label>Name</label>
    	 	<input type = "text" name="name" id = "name" class="form-control" maxlength="200" value="<?php echo $name;?>" placeholder="Enter Your Name" required>
  	 </div>
     <div class="form-group col-md-12">
        <label>Email Id</label>
        <input type = "text" name="email" id = "email" class="form-control" maxlength="200" value="<?php echo $email;?>" placeholder="Enter Your Email" onblur = "checkEmail(this.value)" required>
        <span id="email_err"><?php echo $email_err;?></span>
     </div>
     <div class="form-group col-md-12">
    <label>Gender</label>
    <select name="gender" id = "gender" class="form-control" required >
      <option value="">Select Gender</option>
      <option value="Male" <?php if ($gender == 'Male'){?> selected <?php } ?>>Male</option>
      <option value="Female" <?php if ($gender == 'Female'){?> selected <?php } ?>>Female</option>
      <option value="Other" <?php if ($gender == 'Other'){?> selected <?php } ?>>Other</option>
    </select>
   </div>
     <div class="col-md-12 text-center">
      <input type ="submit" name="submit" class="btn btn-primary" value="Submit">
    </div>
</form>
</div>
<script src = "https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
<script src = "js/validate.js"></script>
</body>
</html>

I have included process_registration.php. Code in process_registration.php will be executed when form is submitted. Note that I have called a JavaScript function checkEmail() on onBlur event of email id. When user enters the email id and leave the field this function will be called. We have included our custom JavaScript file at the end.

Step 4 - Write Ajax script for email validation (validate.js)

Our JavaScript file validate.js has a function named checkEmail(). This function is called as soon as user enters something in the email id field in the form and clicks anywhere else or presses tab. It is a simple Ajax script; it takes the email id as input and runs Ajax to execute a php program check_email.php in the server. Response of Ajax call is written in a span element just below email id field in the form. check_email.php just checks if email id exists in the database from the table candidate. Let's see the code for the function checkEmail().

validate.js


function checkEmail(email)
{ 
  email = email.trim();
    if (email !="") {
      $.ajax({
        type:"POST",
        data:{email:email},
        dataType:"text",
        url:"check_email.php",
        success:function(response) {  
                      $("#email_err").html(response);
                  }
        });
    }
}

Now let's see the code for check_email.php

check_email.php


<?php
include "cfg/dbconnect.php";
$email = trim($_POST['email']);
if (!filter_var($email, FILTER_VALIDATE_EMAIL)) 
      echo  "$email is not a valid email address";
    else {
        $sql = "select email_id from candidate where email_id = '$email'";
        $rs = mysqli_query($conn,$sql);
        if (mysqli_num_rows($rs) > 0)
            echo "Email id already Registered!";
    }
?>

It first validates email id format using filter_var() php function. If email id format is correct, it selects same email id from candidate table. If it finds any, then it sends message as email id already exists.

Let us discuss now what happens when user clicks on submit button. Please read next step.

Step 5 - Process registration form after Submit (process_registration.php)

process_registration.php is included in index.php.


<?php include "process_registration.php";?>

It takes the input values - name, email id and gender and inserts a row with these values into the database table named 'candidate'. Below is the code:

process_registration.php


<?php
  include "cfg/dbconnect.php";
  $name = $email = $gender = $email_err = $msg = "";
  if (isset($_POST['submit'])) {
    $name = trim($_POST['name']);
    $email = trim($_POST['email']);
    $gender = trim($_POST['gender']);
    // validate email id
    if (!filter_var($email, FILTER_VALIDATE_EMAIL)) 
      $email_err = "$email is not a valid email address";
    else {
        // check if email already exists
          $rs = mysqli_query($conn,"select email_id from candidate where email_id = '$email'");
          if (mysqli_num_rows($rs) > 0) {
              $email_err = "Email id already Registered!";
            }
          else {
              $curr_dt = date('Y-m-d');
              $sql = "insert into candidate (name,email_id,gender,registration_dt) values ('$name','$email','$gender','$curr_dt')";
              $result = mysqli_query($conn,$sql);
              if($result) {
                $msg = "Candidate Registered Successfully";
                $name = $email = $gender = "";
              }
              else
                $msg = "Error Occurred, could not register candidate";
            }
        }
      }
?>

Note that during Ajax validation, even after getting email error message, user can go ahead and submit the form, because we are not stopping user to submit the form after Ajax validation. So, in the php code here I am doing same validation again. Reason is to alert user instantly instead of waiting until user enters all data and submits the form.

Step 6 - Add CSS (style.css)

Let us add below styles. I have already added style.css in index.php.

style.css


h4{
  margin-top: 30px;
  margin-bottom: 30px;
  text-align: center;
}
#frm{
  width:40%;
  margin:auto;
}
label{
    font-weight: bold;
  }
#email_err, .error{
	color:red;
}
.success{
	color:green;
}
@media (max-width: 600px){
#frm {
    width: 100%;
  }
}

Keep this style.css in the css folder. Our development is complete let us test it.

Test the Application

Make sure in your XAMPP control panel Apache and MySQL services are running. Open the browser and run localhost/registration. You will see the home page displayed as below:

Check If Email Address Is Already Exists in The Database

Enter Name, Email id and select Gender to submit the form. It should register successfully. Now enter same email id again to register, after entering the email id press tab or select gender. You should see a message displayed below email id field in red color.

Download Source Code

Click on the download button below to get the source code. You can modify the code as per your requirements.

Conclusion

During registration, there could be many fields in the form. For some fields, we can validate values from database and alert user instantly with messages. Here, we have used Ajax to get the data from database and verify if email id already exists. This is fast and the user can instantly correct the data in the field. You can use it for any other validation as well where you need unique values.