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.
DEMO of two linked drop down list

  • Video Tutorial on two linked dropdown list box



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
dd1.php :
Use this if you want to show text box if no matching sub category is found
dd1ck.php :
Back end PHP script for dd1.php. 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 PDO Database connection
config-mysqli.php :
Database connection string , used by dd.php and ddck.php files inside mysqli folder . Read more on MySQLI Database connection
sql-dump.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 dd2_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.
$('#category').change(function(){
//var st=$('#category option:selected').text();
var cat_id=$('#category').val();
$('#sub-category').empty(); //remove all existing options
///////
$.get('ddck.php',{'cat_id':cat_id},function(return_data){
	if(return_data.data.length>0){
	$('#msg').html( return_data.data.length + ' records Found');
$.each(return_data.data, function(key,value){
$("#sub-category").append("<option value='"+value.subcat_id+"'>"+value.subcategory+"</option>");
});
}else{
$('#msg').html('No records Found');
}
}, "json");
///////
});

Backend 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 dd2_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);
?>

Click to Show Script with MySQLi database connection

$result = array();
if($stmt = $connection->prepare("select subcategory,subcat_id from dd2_subcategory where cat_id=?")){
 $stmt->bind_param('i',$cat_id);
 $stmt->execute();
 $stmt = $stmt->get_result();
 $no_of_records=$stmt->num_rows;
 while ($row = $stmt->fetch_assoc()) {
    $result[]=$row;
  }
}else{
  echo $connection->error;
}

$main = array('data'=>$result,'no_of_records'=>$no_of_records);
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  dd2_subcategory where cat_id='$cat_id'")->fetchColumn();

if($no_of_records >=1){
$sql="select subcategory,subcat_id from dd2_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){
	$('#t1').hide();
	$('#sub-category').show();
$.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

If you are using MySQLI extension to manage database, then use the files inside mysqli folder. Update the database login details at config-mysqli.php file.

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.









Subscribe

* indicates required
Subscribe to plus2net

    plus2net.com



    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
    smo1234

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

    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
    smo1234

    02-04-2017

    You need to add single quote to your option value, here in this example we have used id ( numeric data so no space ) , but in your case data have space between. So change this line like this
    $('#city').append("<option value=" + value.city+">"+value.city+"</option>"); // <--------- add options to exixting list box

    Change by adding single quotes

    $('#city').append("<option value='" + value.city+"'>"+value.city+"</option>"); // <--------- add options to exixting list box
    smo1234

    02-04-2017

    Now we have added single quote to the option value so it will work for string data with space between. Download a fresh copy of the zip file.
    Chaitanya

    12-06-2017

    it is not working at all
    smo1234

    15-06-2017

    Checked again , it is working perfectly. Install tables in your database and update login details. You should have JQuery library support.
    srini

    09-07-2017

    I like what you did, but I don't have the sub category for all the categories, so how do I only display as and when required. Then I want to display the form in the same page along with the category and subcategory.
    smo1234

    17-07-2017

    You can manage it the way you want. See the modification for showing text box if no matching record for 2nd list is found. In your case if no matching record is found then don't show the text box.






    Most Popular JQuery Scripts

    1

    Two dependant list boxes

    2

    Calendar with Date Selection

    3

    Data change by Slider

    4

    Show & Hide element


    JQuery Video Tutorials




    We use cookies to improve your browsing experience. . Learn more
    HTML MySQL PHP JavaScript ASP Photoshop Articles FORUM . Contact us
    ©2000-2024 plus2net.com All rights reserved worldwide Privacy Policy Disclaimer