How to create autocomplete live search box using PHP, MySQL and Ajax

Autocomplete or auto-suggestion makes a user's job easy while entering data in a form. It gives a list of suggested values while the user types in an input text box. This is a good option as it saves time for the user during data entry. We can show this type of suggestion in a form using PHP, MySQL and Ajax.

In this topic, we will develop a small application form where a user can enter details along with his/her email ID to submit a form. It will show a list of suggested cities when a user starts typing a city name in the form. Also, if it is a new city, it will be added to the list. We will use jQuery Ajax with PHP in this tutorial.

So how does it work? When the user types the name of a city, an Ajax request is sent to fetch cities starting with the value typed in the city field from the database. The server sends all the matching city names to be displayed in a list box.

Autocomplete Textbox Using PHP, MySQL and jQueryFolders and Files

Below is a screenshot of the folder structure and files we will be using:

Autocomplete textbox in PHP MySQL

We have created a folder named 'hints' under 'xampp/htdocs' and this will be the project root.

Folder 'cfg' - in this folder we have dbconnect.php to connect to MySQL database.
Folder 'css' - custom stylesheet is in this folder
Folder 'js' - in this folder we have our custom JavaScripts file
index.php is the main program for the application. User can enter data and submit the form.
process_application.php is the php code written to process the application. This program works after the form is submitted.

suggest_city.php is the PHP program called by Ajax. This php program selects all city names starting with the letter/s typed in by the user. These city names are selected from cities table in MySQL database

Below is the screenshot of the Application Form

How to create autocomplete textbox using PHP with jQuery AJAX

When a user starts typing in the city of residence field, you will see below suggestions for the city:

php autocomplete textbox from database example

Create the tables in MySQL

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

Table: cities

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

Autocomplete Textbox Using jQuery,PHP And MySQL

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.

cities.sql


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 of each applicant. It stores the name of applicant, email id and applicant's city of residence.

jquery autocomplete

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.

city_of_residence.sql


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.

dbconnect.php


<?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 user can enter the data and submit the form. We will show how we can show a list of suggestion for 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.

index.php


<!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 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

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 onClick event with value of city_name as parameter.

The function selectedCity() is called when user selects a city name from the suggested list. It writes the value of city name in city_name field in 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. User may ignore it and click on any other place of the screen. In that case we need to hide the list. 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 user clicks on the submit button.

Process Application form after Submit (process_application.php)

When the user clicks on 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 new or an existing city. If it is a new city, it will insert a row in cities table. For existing city, it selects the city id from the cities table. Then it inserts a row in city_of_residence table with name, email_id and the city_id. Let's see the code below:

process_application.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"; } } } }

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" added dynamically in the suggest_city.php. CSS properties for them are added in this custom stylesheet. See below:

style.css


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%;
  }
}

php ajax autocomplete search from database exampleTest the Application

Check that Apache and MySQL services are running in XAMPP control panel. Run localhost/hints in the browser. You will see the home page as displayed below:

autocomplete javascript example

Enter Name, Email id and start typing in city or residence field. It should display a list of suggested city names as you start typing, see below:

autocomplete search in php

Now you can select a city or you can enter a new city and submit the form. Verify if new city is appearing in the suggestions.

Hope you could understand all the steps and you could test it successfully.

autocomplete textview in phpNote

Remember that, each time user types a letter, it makes a database call using Ajax (provided data is stored in a database table) and suggested names are displayed. Whereas in a select dropdown, you access database once only and display all values in the list.

php autocomplete textboxDownload Source Code

You can download the source code by clicking on the Download button below.

autocomplete search box in php mysqlConclusion

In this example, you have seen how you can show autocomplete suggestions or hints for a form field using Ajax before submitting the form. You can use it for any similar requirements in your project.