Update select options based on other selected option from database

Question

I have a form which selects regions, provinces, and cities. The select options for provinces depends on the selected region and the options for cities depends on the selected province. All the options are from the database

I have already made the region show up with this code.

<select class="form-control" id="region" name="region">
<?php
if( isset($Region) && count($Region)>0 ){
    foreach($Region as $Region){
        echo '
                <option>'.$Region['region'].'</option>
            ';
    }
}else{
    echo '
                <option>NO RECORDS FOUND</option>
            ';
}
?>
</select>

Could you please help me on my next step which is to display the options for provinces?

<select class="form-control" id="province" name="province">
</select>

I believe after achieving this step I would be able to do the same for displaying cities. Thank you!

Database name: patrol
table name: geography
               >id
               >region
               >province
               >city

I've tried the same as this one by putting this script

<script>
$(function() {
$(document).ready(function () {
    $('#region').change(function() {
       var select = $('#province').empty();
       $.get('script.php', {region: $(this).val()}, function(result) {
           $.each(result, function(i, item) {
               $('<option value="' + item.value + '">' + item.name + '</option>').
                   appendTo(select);
           });
       });
    });
});
});
</script>

and having this script.php

<?php
if (isset($_GET['region'])) {
    $sql = new mysqli('localhost','root','','patrol');
    $region = mysqli_real_escape_string($sql,$_GET['region']);
    $query = "SELECT * FROM geography WHERE region = $region";
    $ret = $sql->query($query);
    $result = array();
    while ($row = $ret->fetch_assoc()) {
         $result[] = array(
             'value' => $row['id'],
             'name' => $row['province']
         );
    }
    echo json_encode($result);
}
?>

Haven't you tried anything yet for displaying the provinces? — Irvin, Dec 16 '16 at 03:01
You would create the `province` select similar to what you did for the `region` select, but using `$_POST['region']` in your query. — Sean, Dec 16 '16 at 03:02
I have tried the same as this one [link](http://stackoverflow.com/questions/18247953/feed-select-options-from-db-depending-on-another-select-options) yet the options for provinces still doesn't seem to work @Irvin — D. Gatch, Dec 16 '16 at 03:03
Then show the code and explain your "still doesn't seem to work" — Irvin, Dec 16 '16 at 03:04
Are you wanting to do this purely in php, where your page reloads on form submit, or using javascript, where the page doesn't reload? — Sean, Dec 16 '16 at 03:04
`$region` is most likely a string, so you need to quote it in your query -> `... WHERE region = '$region'`. — Sean, Dec 16 '16 at 03:12

score 1 · Answer 1 · answered Dec 16 '16 at 05:40

<option>'.$Region['region'].'</option>

You did not set any value in region select option.

It should be like below:

<option value="'.$Region['region'].'">'.$Region['region'].'</option>

also use console.log(result) to see you are getting expected data back or not.

score 0 · Accepted Answer · answered Dec 16 '16 at 06:23

Thank you for answering guys. However, I have found an answer and it did the fix. I'll post the script for others who have the same question as mine

<script type="text/javascript">
$(document).ready(function()
{
 $("#region").change(function()
 {
    var region= document.getElementById("region").value;
    var dataString = 'region='+ region;
  $.ajax
  ({
   type: "POST",
   url: "get_province.php",
   data: dataString,
   cache: false,
   success: function(html)
   {
      $("#province").html(html);
   } 
   });
  });

 $("#region").change(function()
 {
    var province= document.getElementById("province").value;
    var dataString = 'province='+ province;
  $.ajax
  ({
   type: "POST",
   url: "get_city.php",
   data: dataString,
   cache: false,
   success: function(html)
   {
      $("#city").html(html);
   } 
   });
  });
 $("#province").change(function()
 {
    var province= document.getElementById("province").value;
    var dataString = 'province='+ province;
  $.ajax
  ({
   type: "POST",
   url: "get_city.php",
   data: dataString,
   cache: false,
   success: function(html)
   {
      $("#city").html(html);
   } 
   });
  });


});
</script>

Update select options based on other selected option from database

2 Answers2