How to make a dependent dropdown list using jquery Ajax Download

How to make a dependent dropdown list using jquery Ajax, Mysql and PHP PDO, Download full source code

In this tutorial, We will be going to explain How to make a dependent dropdown list using jquery Ajax, Mysql and PHP PDO.

Sometimes we require to make a dependent dropdown list like when you select the category at the same time sub-category will auto-populate in the second dropdown list, It means related to the selected category.

So, when the user will select the category from the category dropdown, the Subcategory will automatically populate in the second dropdown list from the MySQL database. You can implement this example in your web application by following bellow few steps. First, we need to create a database and two tables Category and Subcategory table for the dependent dropdown list using jquery.

So we need to create a database using the below MySQL Command.

CREATE DATABASE dependentdropdown;

After creating the successful database, We need to create the below two tables Category and SubCategory using the below MySQL Command.

1. Create the Category Table

CREATE TABLE `category` (
  `catgID` int(11) NOT NULL,
  `categoryName` varchar(500) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

2. Create the Subcategory Table

CREATE TABLE `subcategory` (
  `subCatgID` int(11) NOT NULL,
  `categoryID` varchar(500) NOT NULL,
  `subCategoryName` varchar(500) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

After creating the successful two tables, we need to add some categories and subcategories records into the database.

3. Create the HTML Form index.php

We need to create two dropdown lists. The HTML code looks like below.

<div class="col-md-4 ml-3">
<div class="row ">
	<div class="col-md-12 py-3 form-bg rounded shadow-sm">
<h4 class="pb-4 text-center">How to Auto populate dropdown with jQuery AJAX</h4>
<form id="login" method="get" action="">
	<div class="form-row">
		<div class="form-group col-md-12">
<select name="category" id="categoryList" class="form-control" onChange="categoryLst();">
<option value="">Select Category</option>
<?php
foreach ($categoryResult as $category) {
	?>
<option value="<?php echo $category["catgID"]; ?>"><?php echo $category["categoryName"]; ?></option>
<?php
}
?>
</select>
		</div>
	</div>
	<div class="form-row">
		<div class="form-group col-md-12">
<select name="subCatg" id="subcatgList" class="form-control">
<option value="">Select Sub Category</option>
</select>
		</div>
	</div>

</form>
	</div>
</div>
</div>

4. Create the javascript

How to load category dependent drop-down list using jQuery AJAX

The categoryLst() function has the parameter value containing the category id. This parameter is for constructing the AJAX request data.

The PHP code will get the records from the subcategory table on the sent category id from the database.

In the below AJAX code, the dependent subcategory dropdown list will get an auto-populated value with the response HTML returned from the PHP code. Download jQuery

<?php 
error_reporting(0);
require_once( "DBConfig.php" );
$dbCategory = new DBsetting();
$query = "SELECT * FROM category";
$categoryResult = $dbCategory->runQuery( $query );
?>
<script src="https://code.jquery.com/jquery-2.1.1.min.js" type="text/javascript"></script>
function categoryLst() {
        var n='';
        var m=document.getElementById('categoryList');
        for (i=0;i< m.length;i++) { 
            if(m[i].selected){
                n += m[i].value + ','; 
            }
        }         
        var n=n.slice(0,n.length -1);
        
	$.ajax({          
        	type: "GET",
        	url: "subcatg.php",
        	data:'catgID='+n,
        	success: function(data){
        		$("#subcatgList").html(data);
        	}
	});
}

5. Create the DBConfig.php file

Now we need to create a DB configuration file for database connectivity. The DB configuration file looks like.

class DBsetting {
	private $host = "localhost";
	private $user = "root";
	private $password = "";
	private $database = "dependentdropdown";
	private $conn;
	
	function __construct() {
		$this->conn = $this->connectDB();
	}
	
	function connectDB() {
		$conn = mysqli_connect($this->host,$this->user,$this->password,$this->database);
		return $conn;
	}
	
	function runQuery($query) {
		$result = mysqli_query($this->conn,$query);
		while($row=mysqli_fetch_assoc($result)) {
			$resultset[] = $row;
		}		
		if(!empty($resultset))
			return $resultset;
	}
	
	function numRows($query) {
		$result  = mysqli_query($this->conn,$query);
		$rowcount = mysqli_num_rows($result);
		return $rowcount;	
	}
}

6. Create the subcatg.php file

The subcatg.php file has the function to get all subcategory from the database based on the category id.

require_once( "DBConfig.php" );
$dbCategory = new DBsetting();
if(!empty($_GET['catgID'])) {
        $catId = $_GET["catgID"];           
	$query ="SELECT * FROM subcategory WHERE categoryID IN ($catId)";
	$results = $dbCategory->runQuery($query);
?>
	<option value="">Select Sub Category</option>
<?php
	foreach($results as $subcategory) {
?>
	<option value="<?php echo $subcategory["subCatgID"]; ?>"><?php echo $subcategory["subCategoryName"]; ?></option>
<?php
	}
}