Check Username Availability in PostgreSQL with AJAX and PHP

With the use of jQuery AJAX you can instantly display the entered username is already been used or not by another user.

User doesn’t have to submit form.

In this tutorial, I show how you can check username if it already exists in the PostgreSQL database or not using jQuery AJAX and PHP.

Check Username Availability in PostgreSQL with AJAX and PHP


Contents

  1. Table structure
  2. Configuration
  3. HTML
  4. AJAX
  5. jQuery
  6. Output
  7. Conclusion

1. Table structure

I am using users table in the example.

CREATE TABLE users (
     id serial PRIMARY KEY,
     username varchar(80) NOT NULL,
     fullname varchar(80) NOT NULL,
     email varchar(80) NOT NULL
)

2. Configuration

Create a new config.php file.

Completed Code

<?php

$host = "localhost";
$user = "postgres";
$password = "root";
$dbname = "tutorial";
$con = pg_connect("host=$host dbname=$dbname user=$user password=$password");

if (!$con) {
   die('Connection failed.');
}

3. HTML

Create a text element to enter username and a <div id="uname_response"> to display availability using jQuery AJAX.

Completed Code

<div>
   <input type="text" class="textbox" id="txt_username" name="txt_username" placeholder="Enter Username" />
   <!-- Response -->
   <div id="uname_response" ></div>
</div>

4. AJAX

Create ajaxfile.php file to handle AJAX requests.

Check if username is POST or not.

If POST then count records where username field value is equal to $username in the users table.

If count is greater than 0 then return Not Available message otherwise, Available message.

Completed Code

<?php
include 'config.php';

if(isset($_POST['username'])){
   $username = $_POST['username'];

   // Check username
   $sql = "select count(*) as allcount from users where username=$1";
   $result = pg_query_params($con, $sql, array($username));
   $row = pg_fetch_assoc($result);
   $count = $row['allcount'];

   $response = "<span style='color: green;'>Available</span>";
   if($count > 0){
      $response = "<span style='color: red;'>Not Available</span>";
   }

   echo $response;
   exit;
}

5. jQuery

Define keyup event on #txt_username.

Send AJAX POST request to ajaxfile.php if value is not empty. Pass {username: username} as data.

On successful callback write a response in #uname_response.

Completed Code

$(document).ready(function(){

   $("#txt_username").keyup(function(){

      var username = $(this).val().trim();

      if(username != ''){

         $.ajax({
            url: 'ajaxfile.php',
            type: 'post',
            data: {username:username},
            success: function(response){

               // Show response
               $("#uname_response").html(response);

            }
         });
      }else{
         $("#uname_response").html("");
      }

   });

});

6. Output

View Output


7. Conclusion

Using the above script you can validate username or email on your form and make sure to again validate the value before inserting the record.

You can view the MySQL version of this tutorial here.

If you found this tutorial helpful then don't forget to share.

Leave a Comment