Autocomplete Live Search Box using PHP, MySQL and Ajax

Create the tables in MySQL

We will create two tables 1) cities and 2) city_of_residence. The table structures are given below:

Table: cities

This table stores all the city names; each city has a unique id.

Table has 2 columns in it.

1. city_id - It is the primary key and auto incremented id of the city
2. city_name - Name of the city

Create table script and insert statements for this table are given below; you can use this code to create the table and populate data in it.

CREATE TABLE `cities` (
  `city_id` int(11) NOT NULL,
  `city_name` varchar(200) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `cities` (`city_id`, `city_name`) VALUES
(1, 'Delhi'),
(2, 'Mumbai'),
(3, 'Kolkata'),
(4, 'Bangalore'),
(5, 'Chennai'),
(6, 'Beijing'),
(7, 'Moscow'),
(8, 'London'),
(9, 'Paris'),
(10, 'Dubai'),
(11, 'Rome'),
(12, 'Tokyo'),
(13, 'Sydney'),
(14, 'Brisbane'),
(15, 'Melbourne'),
(16, 'Perth'),
(17, 'Dhaka'),
(18, 'Islamabad'),
(19, 'Karachi'),
(20, 'Kabul'),
(21, 'Milan'),
(22, 'Munich'),
(23, 'Madurai'),
(24, 'Budapest'),
(25, 'Berlin'),
(26, 'Aurangabad'),
(27, 'Amsterdam'),
(28, 'Meerut'),
(29, 'Dublin'),
(30, 'Edinburgh'),
(31, 'Oslo'),
(32, 'Stockholm'),
(33, 'Pretoria'),
(34, 'Durban'),
(35, 'Cape Town'),
(36, 'Aligarh'),
(37, 'Lisbon'),
(38, 'New York'),
(39, 'Philadelphia'),
(40, 'Chicago'),
(41, 'Detroit'),
(42, 'Los Angeles'),
(43, 'Chandigarh'),
(44, 'San Diego'),
(45, 'Las Vegas'),
(46, 'San Jose'),
(47, 'San Francisco'),
(48, 'Sacramento'),
(49, 'Portland'),
(50, 'St Louis'),
(51, 'Seattle'),
(52, 'Solapur'),
(53, 'Salt Lake City'),
(54, 'Denver'),
(55, 'Dallas'),
(56, 'Houston'),
(57, 'Cleveland'),
(58, 'Moradabad'),
(59, 'Indianapolis'),
(60, 'Miami'),
(61, 'Tampa'),
(62, 'Washington'),
(63, 'Phoenix'),
(64, 'Manchestar'),
(65, 'Bristol'),
(66, 'Tehran'),
(67, 'Prague'),
(68, 'Frankfurt'),
(69, 'Brussels'),
(70, 'Geneva'),
(71, 'Madrid'),
(72, 'Barcelona'),
(73, 'Bucharest'),
(74, 'Buenos Aires'),
(75, 'Cairo'),
(76, 'Hamburg'),
(77, 'Hong Kong'),
(78, 'Jaipur'),
(79, 'Jakarta'),
(80, 'Jodphur'),
(81, 'Johannesburg'),
(82, 'Kanpur'),
(83, 'Kuala Lumpur'),
(84, 'Lahore'),
(85, 'Lucknow'),
(86, 'Manila'),
(87, 'Mexico City'),
(88, 'Rawalpindi'),
(89, 'Rio de Janeiro'),
(90, 'Riyadh'),
(91, 'Sao Paulo'),
(92, 'Shanghai'),
(93, 'Surat'),
(94, 'Shenzhen'),
(95, 'Singapore'),
(96, 'Stockholm'),
(97, 'Toronto'),
(98, 'Hyderabad'),
(99, 'Mecca'),
(100, 'Vienna'),
(101, 'Capetown'),
(102, 'Pune');

ALTER TABLE `cities`
  ADD PRIMARY KEY (`city_id`);

ALTER TABLE `cities`
  MODIFY `city_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=103;

Table: city_of residence

This table stores the city of residence for each applicant. It stores the applicant’s name, email id and applicant's city of residence.

This table has 4 columns in it.

1. id - It is the primary key and auto incremented id
2. name - Name of the applicant
3. email_id - Email of the applicant
4. city_id - City of residence of the applicant. It is the id of city and it comes from cities table.

CREATE TABLE `city_of_residence` (
  `id` int(11) NOT NULL,
  `name` varchar(200) NOT NULL,
  `email_id` varchar(200) NOT NULL,
  `city_id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

ALTER TABLE `city_of_residence`
  ADD PRIMARY KEY (`id`);

ALTER TABLE `city_of_residence`
  MODIFY `id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=1;

PHP code to connect to MySQL database (dbconnect.php)

Use this code to connect to MySQL database. We will use test database for this application.

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

We are using mysqli_connect() function with the below parameters.

1. server - localhost
2. userid - root user
3. password - no password for user root
4. database name - test database.

We will include this dbconnect.php in other php programs. You can also read this topic How to connect to MySQL database in PHP using MySQLi and PDO for more detail about database connection using PHP.

Create an Application form (index.php)

Here we will develop a simple application form where users can enter the data and submit the form. We will show how to show a list of suggestions for the city_of_residence field. For our example, we make this form very simple. Our purpose is to show how you can display hints/suggestions while entering data.

Let's see the code.

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Online Application</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>
  <div class="container">
 <?php 
  $name = $email = $city_name = $name_err = $email_err = $city_err = "";
  include "process_application.php"; 
 ?>

  <h4>New Application</h4>
<form id ="frm" action = "index.php" method = "post">
	<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">
    <div class="error"><?php echo $name_err;?></div>
	</div>
  <div class="form-group col-md-12">
    <label>Email</label>
    <input type = "text" name="email" id = "email" class="form-control" maxlength="200" value="<?php echo $email;?>" placeholder="Enter Your Email">
    <div class="error"><?php echo $email_err;?></div>
  </div>
	<div class="form-group col-md-12">
      <label for="ps">City of Residence:</label>
      <input type  ="text" name="city_name" id="city_name" class="form-control" value="<?php echo $city_name;?>" onkeyup="suggestCity(this.value)">
      <div class="error"><?php echo $city_err;?></div>
       <p  id="sugg_city" class="sugg_city" ></p>
  </div>
	<div class="col-md-12 text-center" style="margin-top: 50px;">
 		<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/script.js"></script>
</body>
</html>

process_application.php will be executed when the form is submitted.

In line 31, we have called a JavaScript function suggestCity() on the onKeyup event of the field. This means, when the user types a city name in this field, onKeyup event will be fired for each letter being typed in. JavaScript function will be called with the value entered in this field. This JavaScript function uses Ajax script to execute a php program and the response will be used to display the suggestions for the city name.

Use Ajax to show suggestions(script.js)

Function suggestCity()

This function is called as soon as user types a letter in the city of residence field. Let's see the code for the function suggestCity().

function suggestCity(city) {
  if(city.trim() == "") {
    $("#sugg_city").fadeOut();
  }
  else {
    city = city.trim();
    $.ajax({  
      type: "POST",  
      url: "suggest_city.php",  
      data: {city:city},
      success: function(response) {
          $("#sugg_city").fadeIn().html(response);
      }
    }); 
  }
}

Using Ajax suggest_city.php (url parameter) executed. Value of the city is used as data parameter for Ajax request. We are writing the response in the <p> tag with id as "sugg_city".

Now let's see the code for suggest_city.php

<?php
include 'cfg/dbconnect.php';
$city = trim($_POST['city']);
if(isset($_POST['city']) && !empty($_POST['city']))  
{
	$sql = "select city_name from cities where city_name like '$city%'";
	$rs = mysqli_query($conn,$sql);
	if (mysqli_num_rows($rs)>0)
	{
	  	echo '<div class="sugg-list">';
	  	foreach ($rs as $arr)
		{
	  		$city_name = trim($arr['city_name']);
	  		echo '<div  id="line1" onClick="selectedCity(\''.$city_name.'\')">'.$city_name.'</div>';
	    }
	  }
}

It fetches all cities with the names starting with the input values in a loop and creates a dynamic list of cities. Also, for each city name, a JavaScript function selectedCity() is called on the onClick event with the value of city_name as parameter.

The function selectedCity() is called when the user selects a city name from the suggested list. It writes the value of the city name in the city_name field of the form. Then it hides the suggestion list. See the below code:

function selectedCity(city_name) {
  $("#city_name").val(city_name);
  $("#sugg_city").hide();
}

We are using jQuery hide() function to hide the list; because we do not want to show the list after a city is selected from the list,

When the user types in the city field, he/she sees the suggested list. The user may ignore it and click on any other place of the screen. In that case, we need to hide the list. The below code will do this:

$(document).click(function (e) {
    if ($(e.target).parents("#sugg_city").length === 0) {
        $("#sugg_city").fadeOut();
    }
});

Note that we have not yet submitted the form. Let us discuss now what happens when the user clicks on the submit button.

Process Application form after Submit (process_application.php)

When the user clicks on the submit button this php program is executed and this is included in index.php.

<?php
  include "cfg/dbconnect.php";
  if (isset($_POST['submit'])){
    $name = trim($_POST['name']);
    $email = trim($_POST['email']);
    $city_name = trim($_POST['city_name']);
    if (empty($name))
      $name_err = "Please enter Name";
    if (empty($email))
      $email_err = "Please enter Email";
    if (empty($city_name))
      $city_err = "Please enter City of Residence";

    // check if city exists, if not insert in city master
    if (!empty($name) && !empty($email) && !empty($city_name)) {
      // validate email id
      if (!filter_var($email, FILTER_VALIDATE_EMAIL)) 
        $email_err = "Invalid email address";
      else {
          // check if email already exists
          $rs = mysqli_query($conn,"select email_id from city_of_residence where email_id = '$email'");
          if (mysqli_num_rows($rs) > 0) {
              $email_err = "Email id already Registered!";
            }
          else {
              $sql = "select city_name, city_id from cities where city_name = '$city_name'";
              $result = mysqli_query($conn,$sql);
              if (mysqli_num_rows($result) > 0 ){  //existing city
                $row = mysqli_fetch_array($result);
                $city_id = $row['city_id'];
              }
              else {   // new city, insert in cities table first and get the id
                  $result = mysqli_query($conn,"insert into cities(city_name) values ('$city_name')");
                  $city_id = mysqli_insert_id($conn);
              }
      
              $sql = "insert into city_of_residence (name,email_id,city_id) values ('$name','$email','$city_id')";
              $result = mysqli_query($conn,$sql);
              if($result) {
                echo "
Form Submitted
";
                echo "Name: ".$name.", Email: ".$email.", City Of Residence: ".$city_name;
                echo "
";
                // reset all values
                $name = $email = $city_name = "";
              }
              else
                echo "Error occurred, please contact Admin";
            }
        }
      }
    }

It checks if the city name is a new or an existing city. If it is a new city, it will insert a row in the cities table. For the existing city, it selects the city id from the cities table. Then it inserts a row in the city_of_residence table with the name, email_id and the city_id. Let's see the code below:

<?php
  include "cfg/dbconnect.php";
  if (isset($_POST['submit'])){
    $name = trim($_POST['name']);
    $email = trim($_POST['email']);
    $city_name = trim($_POST['city_name']);
    if (empty($name))
      $name_err = "Please enter Name";
    if (empty($email))
      $email_err = "Please enter Email";
    if (empty($city_name))
      $city_err = "Please enter City of Residence";

    // check if city exists, if not insert in city master
    if (!empty($name) && !empty($email) && !empty($city_name)) {
      // validate email id
      if (!filter_var($email, FILTER_VALIDATE_EMAIL)) 
        $email_err = "Invalid email address";
      else {
          // check if email already exists
          $rs = mysqli_query($conn,"select email_id from city_of_residence where email_id = '$email'");
          if (mysqli_num_rows($rs) > 0) {
              $email_err = "Email id already Registered!";
            }
          else {
              $sql = "select city_name, city_id from cities where city_name = '$city_name'";
              $result = mysqli_query($conn,$sql);
              if (mysqli_num_rows($result) > 0 ){  //existing city
                $row = mysqli_fetch_array($result);
                $city_id = $row['city_id'];
              }
              else {   // new city, insert in cities table first and get the id
                  $result = mysqli_query($conn,"insert into cities(city_name) values ('$city_name')");
                  $city_id = mysqli_insert_id($conn);
              }
      
              $sql = "insert into city_of_residence (name,email_id,city_id) values ('$name','$email','$city_id')";
              $result = mysqli_query($conn,$sql);
              if($result) {
                echo "
Form Submitted
";
                echo "Name: ".$name.", Email: ".$email.", City Of Residence: ".$city_name;
                echo "
";
                // reset all values
                $name = $email = $city_name = "";
              }
              else
                echo "Error occurred, please contact Admin";
            }
        }
      }
    }

Using mysqli_insert_id() you can get the id of the inserted row. This is the city_id of the new city inserted.

Add CSS (style.css)

We have used some basic styles. We have already added style.css in index.php. Note that class "sugg-list" and id "line1" are added dynamically in the suggest_city.php. CSS properties for them are added in this custom stylesheet. See below:

h4{
  margin-top: 30px;
  margin-bottom: 30px;
  text-align: center;
}
#frm{
  width:30%;
  margin:auto;
}
label{
    font-weight: bold;
  }
.sugg-list{
  height:150px;
  width: 91%;
  overflow-y:auto;
  position:absolute;
  z-index:999;
  cursor:pointer;
  background-color: #fff;
}
#line1:hover{
background-color: #c4b2b2;
}
p{
  text-align: center;
  font-size:20px;
}
.error{
  color: red;
}
@media (max-width: 600px){
#frm {
    width: 100%;
  }
}