Laura Clarke Laura Clarke - 15 days ago 7
MySQL Question

MySQL table populating one dropsown. Upon selection, A second dropdown is populated

Ok, so I've checked the net and the other questions on here and I'm stumped. I've tried a javascript solution from a question posted on here but I think it's not liking MySQL populating the

<option>
s. I'll copy all the code I've got including the javascript I have.

SCRIPT:

<script>
$(function() {
$('#groups').on('change', function() {
var val = $(this).val();
var sub = $('#sub_groups');
$('option', sub).filter(function() {
if (
$(this).attr('data-group') === val || $(this).attr('data-group') === 'SHOW'
) {
$(this).show();
} else {
$(this).hide();
}
});
});
$('#groups').trigger('change');
});
</script>


PHP 1st dropdown:

<select class="form-control" id="groups">
<?php
$sql = "SELECT BoilerBrand FROM boilerbrands";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
echo "<option value='".$row['ID']."'>".$row['BoilerBrand']."</option>";
}
?>
</select>


PHP 2nd dropdown

<select class="form-control" id="sub_groups">
<option data-group='SHOW' value="0">Model</option>
<?php
$sql = "SELECT * FROM boilermodels";
$result = mysql_query($sql);
while ($row = mysql_fetch_array($result)) {
echo "<option data-group='".$row['BoilerBrand']."' value='".$row['BoilerGC']."'>".$row['BoilerModel']."</option>";
}
?>
</select>


Any help with this would be greatly appreciated!

Thanks :)

Answer

The way I normally do this is instead of hiding/showing the options I remove/add them. I believe if you hide the options then the select input can still have that value.

SCRIPT:

<script>
    $(function(){
        <?php
        $sql = "SELECT * FROM boilermodels";
        $result = mysql_query($sql);
        $models = array();
        while ($row = mysql_fetch_array($result)) {
            $models[$row['BoilerBrand']][] = $row;
        }
        /* should look like
        $models = [];
        $models[1][] = ['ModelID'=>'1','BoilerBrand'=>'1','BoilerModel'=>'240E','BoilerGC'=>'47-777-77','BoilerImage'=>'47-777-77.jpg' ];
        $models[1][] = ['ModelID'=>'3','BoilerBrand'=>'1','BoilerModel'=>'290D','BoilerGC'=>'11-111-11','BoilerImage'=>'11-111-11.jpg' ];
        $models[2][]= ['ModelID'=>'2','BoilerBrand'=>'2','BoilerModel'=>'250E','BoilerGC'=>'47-777-77','BoilerImage'=>'47-777-77.jpg' ];
        */
        ?>
        var _boilermodels = '<?php echo json_encode($models); ?>';
        var jsonBoilerModels = JSON.parse(_boilermodels);
        console.log(jsonBoilerModels);

        $('#groups').on('change', function(){
            var $this = $(this);
            var val = $this.val();
            var sub = $('#sub_groups');
            sub.find('option').remove();
            var appendList = [];

            $.each(jsonBoilerModels[val],function(key,value){
                appendList.push('<option value="'.concat(value['BoilerGC'], '">', value['BoilerModel'], '</option>'));
            });

            sub.append(appendList);
        });

        $('#groups').trigger('change');
    });
</script>

1st Dropdown:

<select class="form-control" id="groups">
    <?php
        $sql = "SELECT ID ,BoilerBrand FROM boilerbrands";
        $result = mysql_query($sql);
        while ($row = mysql_fetch_array($result)) {
            echo "<option value='".$row['ID']."'>".$row['BoilerBrand']."</option>";
        }
    ?>
</select>

2nd Dropdown:

<select class="form-control" id="sub_groups">
    <option value="">Select A Model</option>
</select>