<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. $('#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"); /////// });
<?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<?Php
$category=$_POST['category'];
$sub_category=$_POST['sub-category'];
echo "Category : $category <br>
Sub-category = $sub_category ";
?>
$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();
}
Once the text box appears for sub category options, the selection of any item from category for which options available does not change the sub cateogry.
Added this code when records are found for sub category
$('#t1').hide(); $('#sub-category').show();
PDO was used for database connection and managing. Now the same script with MySQLI connection is added and kept inside mysqli folder. Any one can be used.
DEMO Script of this script is added .
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 |