Second dependent dropdown isn't getting filled with data

Question

I'm trying to make a grade distributions website, and I'm creating 4 dropdowns correlating subject (cs, math, etc.), class (data structures, AI, etc.), professor, and quarter the class was taken. After the quarter dropdown is selected, I want to display a bar graph with the data.

The problem I'm running into is that I can't populate the second dropdown with data Basically, I can successfully pull data from the database for the first dropdown, and if the user selects something then the second dropdown (that was originally hidden using jquery) becomes visible, but it isn't properly pulling data from the database and adding it as options to the second dropdown. An example would be that I can select Computer Science from the first dropdown, then the second dropdown is visible, but it doesn't contain 'intro to programming', 'data structures', etc. in it; instead, it's just blank.

FYI, I'm using these selectpickers: http://silviomoreto.github.io/bootstrap-select/

PHP (error is most likely somewhere in the getClasses function, quite possibly the $_POST section of the code):

<?php   

function getSubjects()
{   
    /* Get mysql connect information from external file and connect*/
    require_once 'database.php'; 
    $connection = new mysqli($db_hostname, $db_username, $db_password, $db_database);
    if($connection->connect_error) die ($connection->connect_error);

    /* Get the column containing the subjects from the table */
    $query = 'SELECT DISTINCT Subject FROM gradelist ORDER BY Subject';
    $result = $connection->query($query);

    if(!$result) die ($connection_error);

    /* Keep track of the number of rows in the column; necessary for iterating */
    $rows = $result->num_rows;

    /* selectBar keeps track of the html code for the select Bar*/
    $selectBar = '';

    for($j = 0; $j < $rows; $j++)
    {
        $result->data_seek($j);
        $value = $result->fetch_assoc()['Subject'];
        $selectBar .= '<option>' . $value .'</option>';     
    }

    $result->close();
    $connection->close();

    return $selectBar;
}


function getClasses()
{
    $connection = new mysqli($db_hostname, $db_username, $db_password, $db_database);
    if($connection->connect_error) die ($connection->connect_error);

    if(isset($_POST['subject']))
    {
        $query = "SELECT DISTINCT Class FROM gradelist WHERE Subject = $subject";
        $result = $connection->query($query);
        if(!$result) die ($connection_error);
    }
    else
    {
        die($connection_error);
    }

    $rows = $result->num_rows;

    for($j = 0; $j < $rows; $j++)
    {
        $result->data_seek($j);
        $value = $result->fetch_assoc()['Class'];
        $selectBar .= '<option value = "' . $value . '">' . $value .'</option>';        
    }
    $result->close();
    $connection->close();

    return $selectBar;
}   ?>

HTML Portion of the code (again, the error might be with the $_POST part of the code) :

            <form class="form-horizontal" method = "post" role="form">
                <div class="form-group">
                  <div class="col-lg-10">
                    <select name = "subject" id="subject" class="selectpicker show-tick form-control" data-live-search="true" title ="Subject">
                        <?php echo getSubjects(); ?>
                    </select>
                  </div>
                </div>
            </form>


              <form class="form-horizontal" method = "get" role="form">
                <div class="form-group">
                  <div class="col-lg-10">
                    <select name = "class" id="class" class="selectpicker show-tick form-control" data-live-search="true" title ="Class">
                        <?php if(isset($_POST['subject'])) echo getClasses(); ?>
                    </select>
                  </div>
                </div>
              </form>

jQuery:

$(document).ready(function() {

$('#class').selectpicker('hide');
$('#professor').selectpicker('hide');
$('#quarter').selectpicker('hide');
});

$('#subject').on('change', function(){
    $('#class').selectpicker('refresh');
    $('#class').selectpicker('show');
});
$('#class').on('change', function(){
    $('#professor').selectpicker('show');
});
$('#professor').on('change', function(){
    $('#quarter').selectpicker('show');
});
$('#quarter').on('change', function(){
    showTable();
    temp = $('#class').selectpicker('val') + " with " + $('#professor').selectpicker('val') + " during " + $('#quarter').selectpicker('val');
    $('#displayName').text(temp);
});

Subject would not be set in the first load of your page. You will need to do an ajax call in your JS with either jQuery or JS to get the new data. Or you can submit a form each time but that would be an odd flow I think. This may be an answer you want to view. http://stackoverflow.com/questions/5861090/populating-one-select-box-based-on-the-selection-in-another-select-box-jquery — nerdlyist, Sep 01 '15 at 18:37

score 0 · Accepted Answer · edited May 23 '17 at 10:27

Your PHP is executed with $_POST["subject"] not set, and you never POST the subject the user chose to the page; if you don't make an additional POST request, there's no way for the classes to populate.

One way to do it (without changing any of your files) is like so:

$('#subject').on('change', function(){
    $.post({
        data: { subject: $(this).val() },
        success: function (data) {
            var classes = $(data).find("#class");
            $("#class").replaceWith(classes);
        }
    });
});

So when a change event is triggered on the subject selection, we'll POST the selected subject to the current page. The response should be the entire document generated with the class selection filled (since $_POST["subject"] is set).

We then replace the current page's #class select element with the version in the generated data (wrapped in $() to create DOM elements from the stringified HTML, so we can use find()).

Another way might be to have files, getSubjects.php, getClasses.php, and so on, and POST individually to them (you make the first request onload, and subsequent requests onchange). This way, you can just append the generated option elements to the select elements on the page.

ALSO: Please please please sanitize $_POST["subject"] before using it in a database query. A user could easily add a fake option to the select locally with a malicious string for value, and you'd unknowingly query the DB with that. You can use prepared statements for this (mysqli has the prepare() function to prepare a statement before querying). More on that and combating SQL injection here.

Thank you! This didn't fix the bug, but I understand what you're saying and this will definitely put me on the right track to fix the problem. — Jay, Sep 02 '15 at 06:32

Second dependent dropdown isn't getting filled with data

1 Answers1