根据其他选择选项从DB中选择选项

Question

I have a form that has two select fields, one representing the region and one the name of the city/village/etc. I have a database with all these entries in the following form:

id (int 11, ai)
region (varchar 50) 
city(varchar 50)

I've found a script here on so that lets you select different options but it's made with JS and I have no idea how to adapt it to my needs. I need a database because I have 13.000+ entries and it's not really a good idea to enter them manually in the code. Here is the link to the topic that I've read, it's solution is in one of the comments as well.

how to change a selections options based on another select option selected?.

Please let me know if I need to edit my post before downrating. Thanks in advance!

Answer 1

Just use ajax for this, when one select change fetch data from the server to feed other select.

<select class="select_one">
<?php /* render first select ?>
</select>
<select class="select_two"></select>
<script>
$(function() {

    $('.select_one').change(function() {
       var select = $('.select_two').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 you script.php should return JSON from db:

if (isset($_GET['region'])) {
    $sql = new mysqli('localhost','username','password','database');
    $region = mysqli_real_escape_string($sql,$_GET['region']);
    $query = "SELECT * FROM cities WHERE region = $region";
    $ret = $sql->query($query);
    $result = array();
    while ($row = $ret->fetch_assoc()) {
         $result[] = array(
             'value' => $row['id'],
             'name' => $row['city']
         );
    }
    echo json_encode($result);
}