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

Autocomplete or auto suggesstion makes users' job easy while entering data in a form. It gives a list of suggessted 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 list in a form using PHP, MySQL and Ajax.

In this topic I will develop a small application form where user can enter details along with his/her email id to submit a form. I will show how you can write Ajax code to show a list of cities when user starts typing city name field in the form. Also, if it is a new city, it will be added in the list.

So how it works, when user types the name of the city, an Ajax call is made to fetch city names starting with the name typed in the city field from database. All the matching city names are displayed in a list box just below the city of residence field. If no suggested name can be found no list will appear. If user enters a new name which is not already in the suggestion list, it will be added in the database.

PHP - Ajax Auto Complete SearchAssumption

  1. You must have basic knowledge of HTML, CSS, JavaScript/jQuery and PHP.
  2. You must have basic understanding of Ajax. You can read below topics for more details:
    1. How to install xampp for Windows with setup for PHP development
    2. How to write PHP code with examples for beginners
    3. How to build a simple CRUD application in PHP and MySQL

Autocomplete Textbox Using PHP, MySQL and jQueryFolders and Files

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

Autocomplete textbox in PHP MySQL

I have created a folder named 'hints' under 'xampp/htdocs' as I am using XAMPP. If you are using WAMP, create 'hints' 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 application. User can enter data and submit.
process_application.php is the php code written to process the application. This program works after form is submitted.

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

Below is the screenshot of Application Form

How to create autocomplete textbox using PHP with jQuery AJAX

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

php autocomplete textbox from database example

Step 1 - Create MySQL tables

We will create two tables 1) cities and 2) city_of_residence. I have a database called 'demo'. So these tables will be created in demo database. If you have an existing database other than demo you can also use it. Just make sure same tables do not exist already. 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 application
  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

After you create the table, verify if table structure is same as above. Also make sure all rows are populated.

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

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 script for these table is given below, you can use this code to create the table. You can also download and get the scripts. Please see the download section later in this topic.

city_of_residence.sql

Again after you create the table, verify if table structure is same as above. This table will be initially empty. As we submit the application, this table will be populated with applicants' data.

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

Use below script to connect to the database. Note that we have this database connection php program in 'cfg' folder. This is written once and used in every program where database connection is needed. This will be easy for maintenance and also will enable reusability of the code.

dbconnect.php

I am using mysqli_connect() function which needs 4 parameters.

  1. server - in our case it is localhost
  2. userid - we are using root user
  3. password - no password for user root
  4. database name - demo in our case.

If connection is successful then it will return true; false otherwise. This Boolean value is returned and stored in the $conn variable. We will include this dbconnect.php in other php programs so that we do not need to write it again in the program. For detail database connection understanding please read topic How to Connect to MySQL database in PHP using mysqli_connect.

Step 3 - 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. It is more to show how you can display hints/suggestions while entering data in the form, rather than how you create an application form and submit it. Also, we will use minimum styles here, you are free to add your own styles to make the form look better.

Let's see the code.

index.php

Code in process_application.php will be executed when form is submitted. I have defined the form with three input fields, name, email id and city of residence.

Now see line 31, here I have called a JavaScript function suggestCity() on onKeyup event of city of residence field. This means, when user types to enter city name in this field, onKeyup event will be triggered for each letter being typed in. JavaScript function will be called with the value entered in this field. This JavaScript function will execute Ajax script to call a php program and response from Ajax will be used to display the suggestions for city name.

Step 4 - Write Ajax script to show suggestions(script.js)

Function suggestCity()

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

Ajax script is executed which runs suggest_city.php (url parameter). Value of city is used as data parameter for Ajax call. This value is nothing but the value entered by the user. If Ajax call is a success, I am 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

If it finds one or more rows then it fetches all city names in a loop and creates dynamic list of city names one below the other. Also for each city name a JavaScript function selectedCity() is called on onClick event with value of city_name as parameter.

Function selectedCity() is called when user selects a city name from the suggestion list. It simple writes the value of city name in city_name field in the form. After writing it just hides the suggestion list. See below code:

It is using jQuery hide() function to hide the suggestion list, because after selecting the city name from the list, we do not want to show the list any more.

When user types in the city field, he/she sees the suggestion list. User may ignore it and click on any other place of the form. In that case we need hide the suggestion list. Below code will do this:

Hope you understood how Ajax call is used to show the hints or suggestions. Note that we have not yet submitted the form. Let us discuss now what happens when user clicks on submit button.

Step 5 - Process Application form after Submit (process_application.php)

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

It checks if city name is new or already exists in the cities table in database. If it is a new city, it will insert a row in cities table with the city name and get the id of the city. For existing city, it selects the city id from 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

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

Step 6 - Add CSS (style.css)

We need to add some styles. Very simple and basic styles are used here. You can always add more styles to look better. I 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

php ajax autocomplete search from database exampleTest the Application

Make sure in your XAMPP control panel Apache and MySQL services are running. Open the browser and run localhost/hints. 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 and submit. Or you can enter a new city not in the suggestion list. Submit the form and see if new city entered is appearing in the suggestions. You can test various other cases like, email validation, hiding suggestion list and see if you get expected result.

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

autocomplete textview in phpImportant Note

  1. I have used minimum styles, as our main focus is on the development and not the design of the form how it should look like. There is always a scope to give the screen a better look. You can add your own stylesheet.
  2. Remember that for this suggestion list, each time user types a letter, it makes a database call (provided data is stored in a database table). Whereas in select dropdown, you access database once only and display all values in the list.

php autocomplete textboxDownload Source Code

I have put all codes in a zip file. You can download it by clicking on the Download button below. You do not need to register yourself to download it. You can directly use the code or you can modify them as per your requirements.

autocomplete search box in php mysqlConclusion

In this example I have showed you how you can show autocomplete suggestion or hints for a form field using Ajax before submitting the form. If you develop a form, I am sure you already have seen such requirement. This topic will help you to meet similar requirement in your project.