user3005504 user3005504 - 4 years ago 151
PHP Question

Chained Select Boxes (Country, State, City)

I have a php page where I would like to select a location from a list of countries, states, and cities. The page contains other data for user signup (name, email, etc) so I don't want to refresh the page or anything when the select boxes refresh. Currently, each of the select boxes just load the full list of countries, states, or cities. I want them to be chained so I don't have duplicate city names (same name in different states or countries).

Locations are stored in a database, and are passed to the page on load. They are then looped through and added to the select box:

<tr>
<label>Select State: </label>
<select name="state" id="state_select" style="width:200px;">
<option value="">Select a State or Province</option>
<?php while($state = $states->fetchObject()) { ?>
<option value="<?php echo $state->id; ?>"><?php echo $state->title; ?></option>
<?php } ?>
</select>
</tr>


Database structure is pretty simple:

Country : | id | title |
State : | id | title | country_id |
City : | id | title | state_id |


I can think of logic in an .onChange() statement that should clear the chained select box and append new options, but I am very new to web based languages and I can't get anything working. Below is my attempt, but I guess I can't reference between js and php easily. Note: I am aware that this snippet is really bad and contains errors. My thoughts were to have a script function that does the following:


  1. Clear out all options in the State selection box (assuming you have changed the country selection)

  2. Loop through the supplied list of states (SQL query passed by the controller)

  3. Put a selection option in the State selection box for each entry that has a 'country_id' that matches the selected country_id



^ This would be fantastic if it is possible. Any other methods would be good too, but I have tried using ajax and JSON methods and I honestly don't understand them.

<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>
<script>
$( document ).ready(function() {
$('#country_select').change(function(){
$('#state_select').empty();
//for each state
<?php while($state = $states->fetchObject()) {
$temp = $('#country_select').val());
//if country matches selected country
if($state->country_id == $temp){
// create an option ?>
var option = '<option value="">Test</option>';
<?php// }
//then append that option?>
<?php// } ?>
$('#state_select').append(option);
});
});
</script>

Answer Source

$('#mobile_phone_network option:selected').val()i didn't tested but it should work. It's simple and it will give you some idea

**HTML**
<div id="countryWrap"><select id="country" name="country"></select></div>
<div id="stateWrap"><select id="state" name="state"></select></div>
<div id="cityWrap"><select id="city" name="city"></select></div>

<script>
$(document).ready(function(){
  $('#country').change(function(){
    loadState($(this).find(':selected').val())
  })
  $('#state').change(function(){
    loadCity($(this).find(':selected').val())
  })


})

function loadCountry(){
        $.ajax({
            type: "POST",
            url: "ajax/ajax.php",
            data: "get=country"
            }).done(function( result ) {
                $(result).each(function(){
                    $("#country").append($('<option>', {
                        value: this.id,
                        text: this.name,
                    }));
                })
            });
}
function loadState(countryId){
        $("#state").children().remove()
        $.ajax({
            type: "POST",
            url: "ajax/ajax.php",
            data: "get=state&countryId=" + countryId
            }).done(function( result ) {
                $(result).each(function(){
                    $("#state").append($('<option>', {
                        value: this.id,
                        text: this.name,
                    }));
                })
            });
}
function loadCity(stateId){
        $("#city").children().remove()
        $.ajax({
            type: "POST",
            url: "ajax/ajax.php",
            data: "get=city&stateId=" + stateId
            }).done(function( result ) {
                $(result).each(function(){
                    $("#city").append($('<option>', {
                        value: this.id,
                        text: this.name,
                    }));
                })
            });
}

// init the countries
loadCountry();
</script>




**ajax.php** 

$countryId = isset($_POST['countryId'])  ? $_POST['countryId'] : 0;
$stateId = isset($_POST['stateId'])  ? $_POST['stateId'] : 0;
$command = isset($_POST['get'])  ? $_POST['get'] : "";

switch($command){
case "country":
$statement = "SELECT id, name FROM country";
break;
case "state":
$statement = "SELECT id, name FROM state WHERE country_id=".(int)countryId;
break;
case "city":
$statement = "SELECT id, name FROM country WHERE state_id=".(int)stateId;
break;
default:
break;
}

$sth = $dbh->prepare($statement);
$sth->execute();
$result = $sth->fetchAll();

echo json_encode($result);
exit();
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download