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

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.

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.

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.

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.

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.

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 and PDO.

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.

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

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:

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: