How to dynamically populate a dependent dropdown in PHP using Ajax

Step 1 - Create MySQL tables

We will create two tables named 'category' and 'items' in MySQL database. We will create these tables in the "demo" database which I have created for this project. Table structure and create table scripts are given below:

Table: category

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

Table has 2 columns.

1. category_id - it is the primary key and auto incremented
2. category_name - Name of category like Furniture, Electronics etc.

Create table script for this table is given below:

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.

Table has 3 columns.

1. item_id - it is the 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

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.

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;

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

Use below script to connect to the database.

$server = "localhost";
$userid = "root";
$pwd = "";
$dbname = "demo";
$conn = mysqli_connect($server, $userid, $pwd, $dbname);
//Check connection
if (!$conn) {
    die("Connection Error: " . mysqli_connect_error());
}

We are using mysqli_connect() function with 4 parameters.

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

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

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 (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.

Step 4 - 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:

<?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";
} ?>

Step 5 - Add 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%;
  }
}