Dynamically Populate a Dependent Dropdown in PHP and Ajax

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.

The 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:

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 the items. Each item is attached to a category.

The table has 3 columns.

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

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 the below script to connect to the database.

<?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 the 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 simple, there will be two dropdowns and a submit button. Two dropdowns are for the category and the item (dependent dropdown).

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>

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

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

In line 6, a JavaScript function getItem() is called on the onChange event of the category dropdown with category_id as a parameter. When the 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 used in the url parameter in the Ajax call.

It selects all the items from the items table for the selected category. If it finds one or more rows it creates a dynamic list using an <option> tag. This list is the response from the Ajax call and is 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
	}
}

Users can submit the form after selecting the category and the item. When a user clicks the submit button, we will display the selected values on the screen. We are not going to insert form data into any database table. This is 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)

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%;
  }
}