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. We will use some dummy data in this table.

cities.sql

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

INSERT INTO `cities` (`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 (`id`);

ALTER TABLE `cities`
  MODIFY `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.

city_of_residence.sql

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

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

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

ALTER TABLE `city_of_residence`
  ADD CONSTRAINT `city_of_residence_ibfk_1` FOREIGN KEY (`city_id`) REFERENCES `cities` (`id`) ON DELETE CASCADE ON UPDATE CASCADE;

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 = new mysqli($server, $userid, $pwd, $dbname);
//Check connection
if ($conn->connect_error) 
  	die("Connection Error: ".$conn->connect_error);

$conn is our mysqli object . We will use it in other php programs. You can also read this tutorial 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 use a live search for the city_of_residence. For our example, we make this form very simple. Our purpose is to show how you can display auto suggestions while typing in a form field and submit.

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 href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.2/dist/css/bootstrap.min.css" rel="stylesheet">
  <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";
    ?>

    <h1 class="mb-3 mt-3">Autocomplete Live Search Box</h1>
    <form id="frm" action="" method="post">
      <div class="mb-3">
        <label for="name" class="form-label fw-bold">Name</label>
        <input
          type="text"
          class="form-control"
          name="name"
          id="name"
          value="<?= $name; ?>"
          placeholder="Enter Your Name" />
        <div class="text-danger"><?php echo $name_err; ?></div>
      </div>

      <div class="mb-3">
        <label for="email" class="form-label fw-bold">Email</label>
        <input
          type="text"
          class="form-control"
          name="email"
          id="email"
          value="<?= $email; ?>"
          placeholder="Enter Your Email" />
        <div class="text-danger"><?php echo $email_err; ?></div>
      </div>

      <div class="mb-3">
        <label for="city_name" class="form-label fw-bold">City of Residence</label>
        <input
          type="text"
          class="form-control"
          name="city_name"
          id="city_name"
          value="<?= $city_name; ?>"
          placeholder="Enter Your City"
          autocomplete="off" onkeyup="suggestCity(this.value)" />
        <div class="text-danger" id="city_err"><?= $city_err; ?></div>
        <p id="sugg_city" class="mt-2 sugg_city"></p>
      </div>

      <button
        type="submit"
        class="btn btn-primary"
        name="submit">
        Submit
      </button>

    </form>
  </div>
  <script src="https://code.jquery.com/jquery-3.7.1.min.js"></script>
  <script src="js/script.js"></script>
</body>

</html>

process_application.php, in line 16 will be executed when the form is submitted.

In line 54, we have called a JavaScript function suggestCity() on the onKeyup event of the field. This means, when the user starts typing 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 will execute Ajax code 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);
      }
    }); 
  }
}

suggest_city.php (url parameter) is executed by Ajax request. 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']);
$city = $city . '%';
if(isset($_POST['city']) && !empty($_POST['city'])) {
	$sql = "select city_name from cities where city_name like ?";
	$stmt = $conn->prepare($sql);
	$stmt->bind_param("s", $city);
	$stmt->execute();
	$result = $stmt->get_result();
	if ($result->num_rows > 0) {
	  	echo '<div class="sugg-list">';
	  	foreach ($result as $row) {
	  		$city_name = trim($row['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();
    }
});

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";

    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
          $sql1 = "select email_id from city_of_residence where email_id = ?";
          $stmt1 = $conn->prepare($sql1);
          $stmt1->bind_param("s", $email);
          $stmt1->execute();
          $result1 = $stmt1->get_result();
          if ($result1->num_rows > 0) {
              $email_err = "Email id already exists!";
            }
          else { // check if city exists
              $sql2 = "select * from cities where city_name = ?";
              $stmt2 = $conn->prepare($sql2);
              $stmt2->bind_param("s", $city_name);
              $stmt2->execute();
              $result2 = $stmt2->get_result();
              if ($result2->num_rows == 0 ){
                $city_err = "City does not exist!";  
              }
              else {  // all validations are successful
                  $row = $result2->fetch_assoc();
                  $city_id = $row['id'];  
                  $ins_sql = "insert into city_of_residence (name,email_id,city_id) values (?,?,?)";
                  try{
                    $ins_stmt = $conn->prepare($ins_sql);
                    $ins_stmt->bind_param("ssi", $name, $email, $city_id);
                    $ins_stmt->execute();
                    echo "
Form Submitted
";
                      $name = $email = $city_name = "";
                  }
                  catch(Exception $e){
                    echo "
".$e->getMessage()."
";
                }
              }
            }
          }
        }
      }

There are some validations added for the fields before it inserts the row in the "city_of_residence" table with the name, email_id and the city_id. Let's see the code below:

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:

h1{
  text-align: center;
}
#frm{
  width:30%;
  margin:auto;
}
.sugg-list{
  height:150px;
  overflow-y:auto;
  position:absolute;
  z-index:999;
  cursor:pointer;
  background-color: #fff;
}
#line1:hover{
background-color: #c4b2b2;
}
p{
  text-align: center;
}