Creating and populating a select dynamically

Question

I have 2 tables, Provinces and Districts. I would like to populate a select field with options as District names based on which Province is chosen in another select. The Districts table has a ProvinceID field to reference which Province it belongs to. I know this is doable, I just can't figure it out. I also want to create and update the new Districts select without refreshing the page.

UPDATE: I'm writing it in PHP and MySQL, using jQuery as sparingly as possible.

Answer 1

You didn't state what server side technology you are using (if any). Here's an example in ASP.net - it should point you in the right direction:

http://www.mikesdotnetting.com/Article/97/Cascading-DropDownLists-with-jQuery-and-ASP.NET

Answer 2

I actually figured this out on my own using jQuery and post. On the primary select, I added onchange="getDistricts()" and used this for that function:

function getDistricts()
{
    var province_id = $("#provinces").val();
    $.post("handler.php",
    {
        "mode" : "get_districts",
        "pid" : province_id
    },
    function(data)
    {
        $("#districts").html(data);
    }, "text");
}

And then in handler.php, I have a case that catches the mode, and runs the following code:

<query on districts table>
while($row = $sql->fetchrow($result);
{
    $id = $row['id'];
    $name = $row['name'];
    $html .= "<option value='$id' id='$id'>$name</option>";
}
echo $html;

I'm not a fan of this solution, and would really like something better, but it does what I need it to do for the moment. I hope this can help someone else out.

Answer 3

Make a php script and call it dp.php ( dp, short for data_provider, use any name you like). In dp.php

// get province id passed in via `post` or `get`
$pid = $_REQUEST['pid'];

// get the districts in that province
$query = "SELECT `district_id`, `district` FROM `districts` WHERE province_id` ='$pid'";

// link to your database
$link = mysqli_connect(HOST, USER, PASS, DBNAME);

// execute your query
$result = mysqli_query($link, $query);

// parse the options
while($row = mysqli_fetch_assoc($result)) {
  $options .= '<option value="' . row['district_id'] . '">' . $row['district'] . "</option>\n";
}

// send options
echo $options

With the following markup in your page:

<select id="province" name="province">
  <option value="ny">New York</option>
  ...
</select>

<select id="district" name="district">
</select>

Include the following jQuery:

// whenever a different province is selected
$('#province').change(function() {
  // remove all options in district select
  $('#district').html('');
  // find the new province selected
  var my_province = $('#province').val();
  // get new options from server and put them in your district select
  $('#district').get('path/to/dp.php?pid=' + my_province);
)};

Answer 4

In order to do it without AJAX, prepopulate a Javascript dataset... warning, if you have a lot of data this could be slow, but if it's a manageable list length, you could save some overhead from multiple AJAX requests loading the same data over and over.

var provinces = {};
provinces['province_a_id'] = [
  { name:'District A', id:'district_a_id' },
  { name:'District B', id:'district_b_id' }
];
provinces['province_b_id'] = [
  { name:'District C', id:'district_c_id' },
  { name:'District D', id:'district_d_id' }
];

function getDistricts( referenced_select ) {
  var selected_province = $(referenced_select).val();
  var district_select = $('#districts');
  district_select.empty();
  if ( provinces[selected_province] ) {
    $.each( provinces[selected_province], function(i,v) {
      district_select.append( $('<option value="' + v['id'] + '">').text( v['name'] ) );
    } );
  }
}

$(document).ready( function() {
  $('#provinces').bind( 'change', function() {
    getDistricts(this);
  } );
} );

-- HTML

<select id="provinces" name="provinces">
  <option value="province_a_id">Province A</option>
  <option value="province_b_id">Province B</option>
</select>
<select id="districts" name="districts">
</select>