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 before submitting the form.

In this topic, we 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).

How to dynamically display a dropdown listFolders and Files

We will use below folder structure and files:

dependent drop down list in php and mysql

We will use a folder named 'items' under 'xampp/htdocs'.

  1. Folder 'cfg' is for the database connection, we will use dbconnect.php to connect to MySQL database.
  2. Folder 'css' is for the custom stylesheet.
  3. Folder 'db' is for the sql scripts to create the tables.
  4. Folder 'js' is for custom JavaScripts file.
  5. index.php is the main program for the application. User can select category and item, then submit the form.
  6. get_item.php is the PHP program called from Ajax. This php program selects all items for a selected category from MySQL database.

Below is the screenshot of the Form

dynamic populating the drop down list based on the selected value of first list

Watch YouTube Video

Create MySQL tables

We will create two tables named 'category' and 'items' in MySQL database. Table structure and create table scripts are given below:

Table: category

This table stores all categories. Items are grouped by category.

Dependent Drop-down List in PHP using jQuery AJAX

Table has 2 columns.

  1. category_id - primary key and auto incremented
  2. category_name - Name of category like Furniture, Electronics etc.
Create table script for this table is given below:

category.sql


CREATE TABLE `category` (
  `category_id` int(11) NOT NULL,
  `category_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

INSERT INTO `category` (`category_id`, `category_name`) VALUES
(1, 'Furniture'),
(2, 'Electronics'),
(3, 'Dress Material'),
(4, 'Food');

ALTER TABLE `category`
  ADD PRIMARY KEY (`category_id`);

ALTER TABLE `category`
  MODIFY `category_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=5;

Table: items

This table stores all items. Each item is attached to a category.

PHP Ajax Dependent Dropdown List Example

Table has 3 columns.

  1. item_id - primary key and auto incremented
  2. category_id - category id from category table in which item belogs to
  3. item_name - Name of the item

items.sql


CREATE TABLE `items` (
  `item_id` int(11) NOT NULL,
  `category_id` int(11) NOT NULL,
  `item_name` varchar(100) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;


INSERT INTO `items` (`item_id`, `category_id`, `item_name`) VALUES
(1, 1, 'Table'),
(2, 1, 'Chair'),
(3, 1, 'Shelf'),
(4, 1, 'Sofa'),
(5, 1, 'Bed'),
(6, 2, 'TV'),
(7, 2, 'Mobile'),
(8, 2, 'Laptop'),
(9, 2, 'Digital Clock'),
(10, 2, 'Microwave'),
(11, 3, 'Shirts'),
(12, 3, 'Tee'),
(13, 3, 'Trousers'),
(14, 3, 'Shorts'),
(15, 3, 'Jeans'),
(16, 4, 'Noodles'),
(17, 4, 'Chocolate'),
(18, 4, 'Ice Cream'),
(19, 4, 'Tea'),
(20, 4, 'Coffee');

ALTER TABLE `items`
  ADD PRIMARY KEY (`item_id`);

ALTER TABLE `items`
  MODIFY `item_id` int(11) NOT NULL AUTO_INCREMENT, AUTO_INCREMENT=21;

Connect to the database (dbconnect.php)

Use below script to connect to the database.

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 four parameters.

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

mysqli_connect() will return true (for successful connection) or false for any error. You can also read the topic How to connect to MySQL database in PHP using MySQLi and PDO.

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 (dependent dropdown) respectively. We will use minimum styles here.

Let's see the form below in index.php


<form id ="frm" action = "index.php" method = "post">
  <div class="form-group col-md-12">
    <?php $sql = "select * from category order by category_name";
    $rs = mysqli_query($conn,$sql);?>
    <label>Category</label>
    <select name="category" id="category" class="form-control" onchange="getItem(this.value)" required>
      <option value="">Select Category</option>
      <?php foreach ($rs as $arr) { ?>
        <option value="<?php echo $arr['category_id'];?>" <?php if ($category == $arr['category_id']) {?> selected <?php }?>><?php echo $arr['category_name'];?></option>
            <?php } ?>
    </select>
  </div>
  <div class="form-group col-md-12">
      <?php $sql = "select * from items where category_id='$category' order by item_name";
      $rs = mysqli_query($conn,$sql);?>
      <label for="ps">Item Name:</label>
      <select name="item" id="item"  class="form-control" required>
        <?php foreach ($rs as $arr) { ?>
              <option value="<?php echo $arr['item_id'];?>" <?php if ($item == $arr['item_id']) {?> selected <?php }?>><?php echo $arr['item_name'];?></option>
              <?php } ?>
      </select>
  </div>
  <div class="col-md-12 text-center">
     <input type ="submit" name="submit" class="btn btn-primary" value="Submit">
  </div>
</form>

Category dropdown is populated from category table. So, when the form is opened, it will have list of categories in this dropdown.

If category is already selected, all items for the category are populated in the item dropdown. If no category is selected, item dropdown will be empty.

In line 6, a JavaScript function getItem() is called on onChange event of the category dropdown with category_id as parameter. When user selects any category, getItem() JavaScript function is executed to populate the item dropdown. getItem() will run an Ajax script to call get_item.php which will select all the items for the selected category. We will see this function in the next step.

Write Ajax script to populate items(script.js)

Function getItem()

This function gets the value of category_id from the form and makes an Ajax call to execute get_item.php. Item dropdown is populated using Ajax.


function getItem(category) {
  if (category != "") {
  var str="category="+category;
  $.ajax({ type:"POST",
        data:str,
        url:"get_item.php",
        success: function (response) {
          $("#item").html(response);
        }
    });
  }
  else {  // clear second dropdown
    $("#item").html("");
  }
}

Now let us look at the get_item.php program which is used in url parameter in the Ajax call.

It selects all the items from items table for the selected category. If it finds one or more rows then it creates a dynamic list using <option> tag. This list is actually the response from Ajax call and displayed in the item dropdown. See the below code:

get_item.php


<?php
include "cfg/dbconnect.php";
$category = $_POST['category'];
$sql = "select * from items where category_id = '$category' order by item_name";
$rs = mysqli_query($conn,$sql);
if (mysqli_num_rows($rs) > 0) { ?>
<option value="">Select Item</option>
<?php
	foreach($rs as $arr) { ?>
<option value="<?php echo $arr['item_id'];?>">
    <?php echo $arr['item_name'];?>
</option>
<?php
	}
}

User can submit the form after selecting the category and the item. When user clicks on the submit button, we will just display the selected values on the screen. We are not going to insert form data into any database table. This is just to show if values are selected properly. We will write it in index.php itself.


<?php
include "cfg/dbconnect.php";
$category = $item = "";
if (isset($_POST['submit'])){
  $category = $_POST['category'];
  $item = $_POST['item'];
  $sql = "select a.category_name, b.item_name from category a, items b where a.category_id = b.category_id and a.category_id= '$category' and b.item_id = '$item'";
  $rs = mysqli_query($conn,$sql);
  if (mysqli_num_rows($rs) > 0){
    $row = mysqli_fetch_array($rs);
    echo "<h3>You selected<br>Item Category: <b>".$row['category_name']."</b>, Item Name: <b>".$row['item_name'];
    echo "</b></h3>";
    $category = $item = "";  // clear selected values
  }
  else
    echo "Category and Item not found in database";
} 

Add CSS (style.css)

style.css


h4{
  margin-top: 30px;
  margin-bottom: 30px;
  text-align: center;
}
#frm{
  width:30%;
  margin:auto;
}
label{
    font-weight: bold;
  }
h3{
  text-align: center;
}
@media (max-width: 600px){
#frm {
    width: 100%;
  }
}

ajax dropdown selected value in phpTest the Application

In the browser and run localhost/items. You will see the home page as displayed below:

populate dropdown using jquery

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.

Watch YouTube Video

dynamic dependent select box using jquery ajax and phpDownload Source Code

You can download the source code by clicking the download button below:

dynamic dropdown in phpConclusion

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 in any application where you need to populate a dependent dropdown.