Feed select options from DB depending on another select options

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!

You can store regions in table and city in another table. populate select_city with cities and select_region with regions. When you change the city in select_city send the city id to a php script using ajax, in the php script get the city_id and query the regions table for finding the relevant regions and populate the regions_select — FirmView, Aug 15 '13 at 07:37
@mplungjan I searched for a while but I couldn't find a situation that is similar enough to mine and that I understand. I'm a total noob when it comes to JS related things. — SporeDev, Aug 15 '13 at 07:40
@FirmView Thank you for your answer. However I prefer jcubic's answer because I don't need to change my db structure in any way. Thanks again! — SporeDev, Aug 15 '13 at 07:42

score 1 · Accepted Answer · edited Jan 29 '14 at 15:12

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);
}

Ok. In order to use this I guess I need to change the select_one and select_two according to my select names. Also this code should be in the head of the document, inside the script tags, right? If I got it right I'll give it a try! — SporeDev, Aug 15 '13 at 07:34
Being a total beginner in JS, I can't seem to implement it. Can you provide additional details? Not that your explanation is not clear enough, it's just me. Thanks! — SporeDev, Aug 15 '13 at 10:51
In some cases, the result won't be added to the options list. Then use `$.each(JSON.parse(result)` instead of just `result` — Yohan Blake, Jun 15 '16 at 05:08

Feed select options from DB depending on another select options

1 Answers1

Linked