Jan de Vries Jan de Vries - 1 year ago 85
Ajax Question

cascading dropdown menu with sql ajax

I have 1 sql database with 5 tables. Each table is a subcatagory of the previous one, called: countries, states, cities, ZIPcode, streets.
Now I have 3 dropdowns wich depend on eachother. So when I select 'countries': USA, the next dropdown wil only show USA-states etc. This works. But now I want to extend to 5 dropdowns, so adding 2 more. I don't show what I've tried to add 2 more, because it will probably only make it more complex. So I show the 3 dropdowns that are working now:

file: ajax.php

<?php
//dbConfig is not added here, but it connects to database
include('dbConfig.php');

if(isset($_POST["country_id"]) && !empty($_POST["country_id"])){
//Get all state data
$query = $db->query("SELECT * FROM states WHERE country_id = ".$_POST['country_id']." AND status = 1 ORDER BY state_name ASC");

//Count total number of rows
$rowCount = $query->num_rows;

//Display states list
if($rowCount > 0){
echo '<option value="">Select state</option>';
while($row = $query->fetch_assoc()){
echo '<option value="'.$row['state_id'].'">'.$row['state_name'].'</option>';
}
}else{
echo '<option value="">State not available</option>';
}
}

if(isset($_POST["state_id"]) && !empty($_POST["state_id"])){
//Get all city data
$query = $db->query("SELECT * FROM cities WHERE state_id = ".$_POST['state_id']." AND status = 1 ORDER BY city_name ASC");

//Count total number of rows
$rowCount = $query->num_rows;

//Display cities list
if($rowCount > 0){
echo '<option value="">Select city</option>';
while($row = $query->fetch_assoc()){
echo '<option value="'.$row['city_id'].'">'.$row['city_name'].'</option>';
}
}else{
echo '<option value="">City not available</option>';
}
}
?>

****The index.php-file**** (I didn't add the css):

<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<script src="jquery.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$('#country').on('change',function(){
var countryID = $(this).val();
if(countryID){
$.ajax({
type:'POST',
url:'ajaxData.php',
data:'country_id='+countryID,
success:function(html){
$('#state').html(html);
$('#city').html('<option value="">Select state first</option>');
}
});
}else{
$('#state').html('<option value="">Select country first</option>');
$('#city').html('<option value="">Select state first</option>');
}
});

$('#state').on('change',function(){
var stateID = $(this).val();
if(stateID){
$.ajax({
type:'POST',
url:'ajaxData.php',
data:'state_id='+stateID,
success:function(html){
$('#city').html(html);
}
});
}else{
$('#city').html('<option value="">Select state first</option>');
}
});
});
</script>
</head>
<body>
<div class="select-boxes">
<?php
//Include database configuration file
include('dbConfig.php');

//Get all country data
$query = $db->query("SELECT * FROM countries WHERE status = 1 ORDER BY country_name ASC");

//Count total number of rows
$rowCount = $query->num_rows;
?>
<select name="country" id="country">
<option value="">Select Country</option>
<?php
if($rowCount > 0){
while($row = $query->fetch_assoc()){
echo '<option value="'.$row['country_id'].'">'.$row['country_name'].'</option>';
}
}else{
echo '<option value="">Country not available</option>';
}
?>
</select>

<select name="state" id="state">
<option value="">Select country first</option>
</select>

<select name="city" id="city">
<option value="">Select state first</option>
</select>
</div>
</body>
</html>

Answer Source

You need to create a two more table zipcode and streets and add a city_id in zipcode table and zip_id in streets table

 <select name="zipcode" id="zipcode">
     <option value="">Select Zipcode first</option>
 </select>

 <select name="streets" id="streets">
      <option value="">Select Streets first</option>
 </select>

Jquery Scrtipt

$('#city').on('change',function(){
    var cityId = $(this).val();
    if(cityId){
        $.ajax({
            type:'POST',
            url:'ajaxData.php',
            data:'city_id='+cityId,
            success:function(html){
                $('#zipcode').html(html);
            }
        }); 
    }else{
        $('#zipcode').html('<option value="">Select zipcode first</option>'); 
    }
});
});

$('#zipcode').on('change',function(){
    var zipId = $(this).val();
    if(zipId){
        $.ajax({
            type:'POST',
            url:'ajaxData.php',
            data:'zip_id='+zipId,
            success:function(html){
                $('#streets').html(html);
            }
        }); 
    }else{
        $('#streets').html('<option value="">Select Streets first</option>'); 
    }
});
});

Php code is same as state just need to change table name and fields

By using this you get the cascading dropdown menu of zipcode and streets.

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download