Populating secondary dropdown (select) contents depending on selected primary dropdown.

dojocasino

I have a table for student exam results. When a user creates a new entry in the table, I want them to first select a student from a dropdown, and then for a secondary dropdown to become populated with the list of course modules that this particular student has taken.

Currently the modules dropdown is populated with all of the modules available, but I want to restrict this list just to modules that the student has actually taken. In order to do this I assume I would need to repopulate the modules list somehow from an onchange event attached to the students list, but I'm uncertain how to go about this.

Screenshot

if (!isset($_POST['action'])) {
    // Get a list of values for the select lists

    $data['ModuleCodes'] = $db
            ->selectDistinct('Modules', 'ModuleID as value, ModuleCode as label')
            ->fetchAll();

    $data['CWOutcomes'] = $db
            ->sql( 'SELECT ID as value, Outcome as label FROM CourseWorkOutcomes ORDER BY label ASC')
            ->fetchAll();

    $data['CourseParts'] = $db
            ->selectDistinct('CourseParts', 'PartID as value, PartName as label')
            ->fetchAll();

    $data['ExamBoards'] = $db
            ->sql("SELECT ID as value, ExamBoard as label FROM ExamBoards WHERE ID = '17' ")
            ->fetchAll();

    $data['Students'] = $db
            ->sql( "SELECT StudentID as value, (Surname + ', ' + Forename) AS label FROM Student ORDER BY label ASC")
            ->fetchAll();
}

initComplete: function (settings, json) {
                // Populate the site select list with the data available in the
                // database on load
                editor.field('ExamBoardResults.CWOutcomeID').update(json.CWOutcomes);
                editor.field('ExamBoardResults.ModuleID').update(json.ModuleCodes);
                editor.field('ExamBoardResults.ExamBoardID').update(json.ExamBoards);
                editor.field('ExamBoardResults.StudentID').update(json.Students);
}

dojocasino

I've made a little progress with this problem with help from this discussion, however I'm still experiencing issues that may be related to either my SQL query or returned JSON array.

$('select', editor.field('ExamBoardResults.StudentID').node()).on('change', function () {
    alert("student id: " + editor.field('ExamBoardResults.StudentID').val());

    $.ajax({
            url: "includes/GetModuleByStudent.php?studentID=" + editor.field('ExamBoardResults.StudentID').val(),
            success: function (result) {
            alert(result);
            editor.field('ExamBoardResults.ModuleID').update(result);
            }});
});

$studentID = ($_GET['studentID']);

$sql  = "SELECT ModuleID FROM MScPublicDev.dbo.StudentsByModuleView WHERE StudentID = {$studentID} ";

$studentModuleArray = array();

$result = sqlsrv_query($conn, $sql);

while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_ASSOC)) {
    $studentModuleArray[] = array(
        "label" => $row['ModuleID'],
        "value" => $row['ModuleID']
        );
}

$JSONOutput = json_encode($studentModuleArray);

echo $JSONOutput;

Once the user selects a student from the list, the browser alerts the Student ID and passes it to the PHP. Once it has received data from the PHP it alerts the array to the screen, and should also update the Modules list with the array data. While it is alerting the array correctly, I get the following error in Chrome's console log...

Uncaught TypeError: Cannot use 'in' operator to search for '108' in [{"label":"000001","value":"000001"},{"label":"000002","value":"000002"},{"label":"000006","value":"000006"}]

..and the modules select list is not populated with the data. Is the array formatted incorrectly or am I missing something else?