How to dynamically populate a dependent dropdown in PHP using Ajax
When user selects a value from a dropdown list, a dependent dropdown can be populated from the database based on value from the first dropdown. By using Ajax with PHP and MySQL, we can populate this dropdown dynamically without submitting the form.
In this topic, I will develop a simple application using which user can select a category (first dropdown) and all items under the selected category will automatically be populated in the second dropdown (dependent dropdown).
You must have basic understanding of Ajax.You can read below topics for more details:
Let us create two tables named 'category' and 'items' in MySQL database. 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 structure and create table scripts are given below:
This table stores all categories. Items are grouped by category.
Table has 2 columns.
category_id - it is the primary key and auto incremented
category_name - Name of category like Furniture, Electronics etc.
Create table script for this table is given below, you can use this code to create the category table.
After you created the table and inserted data in it, please verify if data is inserted correctly, it should have below data in it:
This table stores all items. Each item is attached to a category.
Table has 3 columns.
item_id - it is the primary key and auto incremented
category_id - category id from category table in which item belogs to
item_name - Name of the item
Create table script for this table is given below, you can use this code to create the table. If you download the zip file (see download section below), you will get all the scripts.
After you create the table and inserted data in it, verify if data is inserted correctly, it should have below data in it:
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 and false otherwise. 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 a form to select category and item (index.php)
Our form is very simple, there will be only two dropdowns and a submit button. Two dropdowns are for category and item. I will use minimum styles here; you are free to add your own styles to make the form look better.
Let's see the form below in index.php
For category, I have selected all category names from category table and then populating the dropdown. So, when the form is opened, it will have list of categories in the dropdown.
For item dropdown, if category is already selected, I am selecting all items for the category and populating the item dropdown. This is when form is opened. If no category is selected, item dropdown will be empty.
Step 4 - Write Ajax script to populate items(script.js)
This function gets the value of category_id from the form and makes an Ajax call to execute get_item.php. Output of get_item.php is displayed in the item field of the form.
Now let us look at the get_item.php program which is used in url parameter in the Ajax call.
Hope you understood how Ajax call is used to get data in a dropdown. Let's see now what happens when user clicks on submit button.
User can submit the form after selecting category and item. When user clicks on submit button, I will just display the selected values on the screen. I am not inserting form data into any database table. This is just to show if values are selected properly. I am not creating a separate program for this, I wrote it in index.php itself.
So, below is the complete code of index.php
Step 5 - 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. See below:
Test the Application
Make sure in your XAMPP control panel Apache and MySQL services are running. Open the browser and run localhost/items. You will see the home page as displayed below:
After the form is displayed, by clicking on category dropdown, verify that category dropdown is populated with values from category table. Click item dropdown, it should be empty. Now select any category and check that items for that category are populated in the item dropdown.
After selecting a category and an item if you submit the form you will see selected category name and item name are displayed on top of the form.
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 topic I have explained you how you can dynamically populate a dropdown from database using Ajax, based on a particular value from another dropdown. Here item dropdown is dependent on category dropdown and populated using Ajax. You can use this for any other types of fields with similar behaviour.