Jan de Vries Jan de Vries - 3 months ago 31
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

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.