Linked 2nd list box depending on selection of 1st one using JQuery, PHP, JSON

Double Drop down linked listbox

Before reading this you must understand how to populate dropdown list box by using records of a table.
Here we will use two list boxes in which the 2nd listbox ( sub category ) is shown based on the selection of 1st listbox ( category ). Both the drop down boxes uses records from two different tables.

Here are the files used in the script

dd.php :
main front end script which displays two selections
ddck.php :
Back end PHP script to collect matching options for second list. Returns data as JSON string
dd-submit.php :
Receives the selected options after submission of the form.
config.php :
Database connection string , used by dd.php and ddck.php files. Read more on Database connection
sql-sump.txt :
Use this sql dump to create tables used in this script.

dd.php : Showing the list box and jQuery

This file populates the first drop down list ( category ) by taking records from category table. For this it connects to database and collects all the records and add them as options to category list box.
Category name is displayed as Text part and cat_id is taken as options.
Here is the code to populate the 1st list box ( note: jQuery is not used )
<form method=post action=dd-submit.php>
<select name=category id=category>
<option value='' selected>Select</option>
<?Php
require "config.php";// connection to database 
$sql="select * from category "; // Query to collect data 

foreach ($dbo->query($sql) as $row) {
echo "<option value=$row[cat_id]>$row[category]</option>";
}
?>
</select>
<select name=sub-category id=sub-category>
</select>
<input type=submit value=Submit></form>
Now we have seen in our previous listbox tutorial how to collect user selection of a list box. Here also once the category is selected by user the change function get fired.
$('#category').change(function(){
Inside this function we will collect the user selection and store it a JavaScript variable cat_id . If required we can also collect text part of the selection, this part is commented in the present script however you can use them instead of selected options.
var cat_id=$('#category').val();
After getting the variable now we will send it to backend script (ddck.php) and get new set of sub categories for our second dropdown , before that we will remove the existing options if any for the second dropdown.
$('#sub-category').empty();
Now we will use get method to post cat_id to backend script ddck.php and get all the matching records. These records are added as option to our second list box with id = sub_category.
$.get('ddck.php',{'cat_id':cat_id},function(return_data){
When we receive the data for sub category we will get JSON string so we need to loop through and add each option to drop down listbox.
$.each(return_data.data, function(key,value){
$("#sub-category").append("
<option value=" + value.subcat_id +">"+value.subcategory+"</option>
"); }); }, "json");
You can read more on how to add option to an existing dropdown list box.
Now the above process repeats once the first dropdown of category is changed by user.

Backed script ddck.php

This page receives the posted value of cat_id and collects the matching records from sub_category table. The output is posted back as JSON string to calling page ( dd.php ) . Here is the complete code.
<?Php
@$cat_id=$_GET['cat_id'];
//$cat_id=2;
/// Preventing injection attack //// 
if(!is_numeric($cat_id)){
echo "Data Error";
exit;
 }
/// end of checking injection attack ////
require "config.php";

$sql="select subcategory,subcat_id from subcategory where cat_id='$cat_id'";
$row=$dbo->prepare($sql);
$row->execute();
$result=$row->fetchAll(PDO::FETCH_ASSOC);

$main = array('data'=>$result);
echo json_encode($main);
?>
dd-submit.php
After selecting category and subcategory the user can submit the form. The data is collected and displayed inside dd-submit.php page.
<?Php
$category=$_POST['category'];
$sub_category=$_POST['sub-category'];

echo "Category : $category <br> 
Sub-category = $sub_category ";

?>

Showing textbox in place of 2nd dropdown list

Sometime there may not be any matching data for the 2nd list to populate. In such a case we can ask user to enter their data in a text box. In this condition we will monitor the number of records found for 2nd list box. If number of records found is equal to zero then by using JQuery we will hide the dropdown listbox and show the text box. AT the page loading time this new text box we will keep inside the form and make it as hidden. Once the number of records found to be equal to zero we will hide the second dropdown listbox and show the text box.

For this modification there are two different files dd1.php and dd1ck.php placed inside your downloaded zip folder

Inside dd1ck.php file instead of directly collecting the sub – category details we will first check number of matching records returned and if the value is not equal to zero then we will collect records. Now we are returning number of records found to main script along with sub –category options.
$no_of_records = $dbo->query("select count(cat_id) from  subcategory where cat_id='$cat_id'")->fetchColumn();

if($no_of_records >=1){
$sql="select subcategory,subcat_id from subcategory where cat_id='$cat_id'";
$row=$dbo->prepare($sql);
$row->execute();
$result=$row->fetchAll(PDO::FETCH_ASSOC);
}else{
$result='';
}

$main = array('data'=>$result,'no_of_records'=>$no_of_records);
echo json_encode($main);
In our JQuery script we will check number of records found than accordingly we decide to show dropdown list box or a text box for user to enter data.
if(return_data.no_of_records>=1){
$.each(return_data.data, function(key,value){
$("#sub-category").append("<option value=" + value.subcat_id +">"+value.subcategory+"</option>");
});
}else{
/// add text box and hide 2nd subcategory 
$('#sub-category').hide();
$('#t1').show();
}

How to Install the script.

Open config.php file and enter your database login details.
Create tables by using sql-dump.txt file inside phpmyadmin
Open dd.php file through your server .
Open dd1.php file to use the script with textbox option as described above

This tutorial uses the concepts adding option to list box , posting data using get method and JSON parsing of string to get data. You can further extend to create three dropdown list linking to each other.

Your Rating


Google+
Ankit Sinha

26-03-2016

Hi, I am getting ID instead of Name of the Sub Category on dd-submit.php.

How can i echo Sub_Category name
subhendu

07-04-2016

Check for this line
<option value=" + value.subcat_id +">"+value.subcategory+"</option><br /> Here we are using value.subcat_id to transfer id. Change this to value.subcategory to get the subcategory name instead of id
patty

13-06-2016

How can I echo Category name instead of Category ID? pls need ur help asap.
subhendu

20-06-2016

From database you can change this line
<option value=$row[cat_id]>$row[category]</option>
to
<option value=$row[category]>$row[category]</option>


Tommy Andersen

22-08-2016

Just a hint :-) Beware, if you change the $row[cat_id] to $row[category] then it breaks the dd.php test page because the subcategory no longer functions as it relies on the cat_id to fetch subcategories.
I managed to display the category value and the subcategory value on the submit page by running a sql query for the category and thus get the value and not the ID. The subcategory can be changed without any problems.
Not the most elegant solution but it works :-)
joe pinai

17-11-2016

tommy mind sharing ur code, i can't figure out how. stuck for 4 days at this

Post Comment This is for short comments only. Use the forum for more discussions.







Most Popular JQuery Scripts

1

Two dependant list boxes

2

Calendar with Date Selection

3

Data change by Slider

4

Show & Hide element
HTML . MySQL. PHP. JavaScript. ASP. Photoshop. Articles. FORUM Contact us
©2000-2017 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer