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.
You must have basic understanding of Ajax. You can read below topics for more details:
Below is a screenshot of the folder structure and files I am using:
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'.
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
When user starts typing in city of residence field, you will see below sugegsstions for the city:
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:
This table stores all the city names, each city has a unique id.
Table has 2 columns in it.
city_id - It is the primary key and auto incremented id of the application
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.
id - It is the primary key and auto incremented id
name - Name of the applicant
email_id - Email of the applicant
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.
server - in our case it is localhost
userid - we are using root user
password - no password for user root
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.
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.
Step 4 - Write Ajax script to show suggestions(script.js)
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
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:
Test 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:
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:
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.
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.
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.
Download 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.
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.