Populate dropdown list based on selection in another dropdown

Question

First of all I would like to say that there are many questions like this one and this is a duplicated question but I tried to do it by myself by looking at other codes but I gave up after 1 hour.

What I want to do is to populate dropdown list based on selection in another dropdown but both dropdown's options are coming from database.

I don't know jQuery and I also saw that some people used onChange function to do what I needed to do.

So, I would like to explain my problem. I have 2 dropdown. One of them is containing the school names and other one contains the course codes. What I need to do is, I want to show course codes based on the school you choose. Because every school has its own courses.

This is the first dropdown. foreach loop may look confusing. I simply echo options but if user selected school already, I echo user's school as selected. That's why I have if else statement there.

$query_schools = "SELECT * FROM SCHOOLS ORDER BY SCHOOL_TYPE ASC";
$query_users = "SELECT USER_SCHOOL FROM USERS WHERE USER_ID = $user1_id";

$schools_result = mysqli_query($dbConnection, $query_schools);
$users_result = mysqli_query($dbConnection, $query_users);

while($data = mysqli_fetch_assoc($users_result)){ $user_school = $data['USER_SCHOOL']; }

foreach($schools_result as $school_result){
    if($user_school == $school_result['SCHOOL_NAME']){
        echo "<option value='$school_result[SCHOOL_SHORT_NAME]' selected>$school_result[SCHOOL_NAME]</option>";
    }else{
        echo "<option value='$school_result[SCHOOL_SHORT_NAME]'>$school_result[SCHOOL_NAME]</option>";
    }
}

This is the second drop down and they are similar.

$query_programs = "SELECT * FROM PROGRAMS ORDER BY PROGRAM_CODE ASC";
$query_users = "SELECT USER_PROGRAM FROM USERS WHERE USER_ID = $user1_id";

$programs_result = mysqli_query($dbConnection, $query_programs);
$users_result = mysqli_query($dbConnection, $query_users);

while($data = mysqli_fetch_assoc($users_result)){ $user_program = $data['USER_PROGRAM']; }

foreach($programs_result as $program_result){
    if($user_program == $program_result['PROGRAM_CODE']){
        echo "<option value='$program_result[PROGRAM_CODE]' title='$program_result[PROGRAM_NAME]' selected>$program_result[PROGRAM_CODE]</option>";
    }else{
        echo "<option value='$program_result[PROGRAM_CODE]'>$program_result[PROGRAM_CODE]</option>";
    }
}

if it was pure php, I chould do like this for the first query:

$query_programs = "SELECT * FROM PROGRAMS WHERE PROGRAM_SCHOOL = '$user_school' ORDER BY PROGRAM_CODE ASC";

Thank you for your help.

Answer 1

On Stackoverflow there is an example that has a jsfiddle attached: Populate one dropdown list based on the selection of other dropdown list

It works by creating one already populated select and one EMPTY one:

<select id="cat">
    <option val="car">car</option>
    <option val="phone">phone</option>
</select>
<select id="item">
</select>

The first select (Id=cat) has a function attached, which basically is jquery's way of "onChange":

(function() {
      $('#cat').change(function(){
        populateSelect();
    });
});

so whenever you CHANGE (i.e. select another item of the "id=cat" selection box it calls the next function which then populates the other select box "id=item" with new "<option .... option>" values:

cars=new Array("Mercedes","Volvo","BMW","porche");
phones=new Array('Samsung','Nokia','Iphone');

function populateSelect(){
    // get the value that was selected so we can populate the other one
    cat=$('#cat').val();
    // clear the other select box
    $('#item').html('');
    // now fill with new code depending on the slection
    if(cat=='car'){
        // this is jquery's way of a for loop
        cars.forEach(function(t) { 
            // $('#item') refers to the EMPTY select list
            // the .append means add to the object refered to above
            $('#item').append('<option>'+t+'</option>');
        });
    }
    elsif(cat=='phone'){
        phones.forEach(function(t) {
            $('#item').append('<option>'+t+'</option>');
        });
    }
}

Answer 2

I am not pretty much sure about php server side coding but I can tell you how you can achieve load data from ajax.

Say you have below 2 select

<select class='school'>
     <option value="sc1">school1</option>//Place your school codes in value for option
     <option value="sc2">school2</option>
     <option value="sc3">school3</option>
</select>

<select class="course">
</select>

Now on change of first dropdown .school perform an ajax call as below:

$('.school').on('change',function()
{
      var selectedSchool=$(this).find('option:selected').val(); //Select the school code which is stored as value for option
      $.ajax({
             url:'/SomeFunction/', //Write a function in the server side which accepts school code as argument
             type:'POST',
             dataType:'json',//return type from server side function [return it as JSON object]
             contentType: "application/json",
             data: JSON.stringify(selectedSchool), //Pass the data to the function on server side
             success: function (data) { //Array of data returned from server side function
                    $.each(data,function(value){
                          $('.course').append('<option>'+value+'</option>');
                    });
             },
             error:
                function (data) {
                    //display any unhandled error
             }
       });
});

Answer 3

When you are echoing-in the OPTION statements give each type of course code a unique value, and in the second select box give each option a matching class.

Select box 1 would have:

echo "<SELECT onchange='changeCourseCodes(this)'>"
echo "<OPTION value='UNIQUE1' name='Course Type 1'>"
//... more options, then close select

Then be sure to give an ID to the second select box, the one which displays the changing values:

echo "<SELECT id='courseCodes'>"
echo "<OPTION class='UNIQUE1'>"
// options, end select, etc.

As for the jQuery, you could show just one of the OPTION types, hiding the rest with css and use this command to hide/show the other list when the option is changed.

<script>
  function changeCourseCodes(selectedOption)
  {
     var classType = $(selectedOption).val(); // get the selected class type
     $('#courseCodes > OPTION').hide(); // hide all options
     $('.' + classType).show(); // show just the chosen ones
  }
</script>

This is UNTESTED code though, a stab in the dark. Something along these lines will get it done.

Answer 4

I have implemented the same using javascript. In my case the input is json. You can just pass the input to the onchange function call or use ajax within the function.

HTML

<table>
<tr>
    <td>
        <select onchange="f(event)">
            <option value="" disabled selected style="display:none;">--Select--</option>
            <option>Offshore</option>
            <option>Onsite</option>
        </select>
    </td>
    <td>
        <select>
            <option>------</option>
        </select>
    </td>
</tr>
   <tr>
    <td>
        <select onchange="f(event)">
            <option value="" disabled selected style="display:none;">--Select--</option>
            <option>Offshore</option>
            <option>Onsite</option>
        </select>
    </td>
    <td>
        <select>
            <option>------</option>
        </select>
    </td>
</tr>

Javascript

 window.f=function(event){
 onoffelem=event.target;
 locelem=event.target.parentNode.nextSibling.nextSibling.children[0];
 locelem.options.length=0;
 offmap=JSON.parse('{"11":"Chennai","12":"Kolkatta"}');
 onsitemap=JSON.parse('{"1":"USA","2":"Canada"}');
 selectedVal=onoffelem.options[onoffelem.selectedIndex].text;
    if(selectedVal=="Offshore"){
         for(var key in offmap){
             option = document.createElement( 'option' );
             option.value = key;
             option.text = offmap[key]
             locelem.add(option);   
         }
    }else{
        for(var key in onsitemap){
             option = document.createElement( 'option' );
             option.value = key;
             option.text = onsitemap[key]
             locelem.add(option);   
         }
    }

}

JSFiddle Demo