Pull dropdown Options from a DB based on selection in another dropdown

Question

I have two dropdown lists as a part of a form I'm creating, both of which have options that are being pulled from a mysql database. I would like the options in the second dropdown to change based on the selection in the first dropdown. I know how to do this using Javascript when the second list is static, but I would like both dropdowns to dynamically pull from the database. Below is the HTML and Javascript I'm currently using. Any ideas would be great.

HTML:
<form>
    <label for="org_name">Organization Name:</label>
    <select id="org_name" name="org_name" onchange="configureDropDownLists(this,'submitter_name')">
        <option value="empty">&nbsp;</option>
        <?php
            mysql_connect("database", "username", "password") or die(mysql_error ());
            mysql_select_db("databaseName") or die(mysql_error());
            $query = "SELECT * FROM Table";
            $result = mysql_query($query);
            while($row = mysql_fetch_array($result)){
                echo "<option value='" . $row['org_name'] . "'>" . $row['org_name'] . "</option>";
            }
            mysql_close();
        ?>
    </select>
    <label for="submitter_name">Request Submitted By:</label>
    <select id="submitter_name" name="submitter_name">
        <option value="empty">&nbsp;</option>
    </select>
    <input type="Submit" value="Submit">
</form>

Javascript:

function configureDropDownLists(org_name,submitter_name) {
    var org = new Array('Submitter 1', 'Submitter 2');

    switch (org_name.value) {
        case 'org':
            document.getElementById(submitter_name).options.length = 1;
            for (i = 0; i < org.length; i++) {
                createOption(document.getElementById(submitter_name), org[i], org[i]);
            }
            break;
        default:
            document.getElementById(submitter_name).options.length = 1;
            break;
    }

    createOption(document.getElementById(submitter_name), 'Other', 'Other');
        if (org_name.value === 'empty') {
            document.getElementById(submitter_name).options.length = 1;
    }
}


function createOption(ddl, text, value) {
    var opt = document.createElement('option');
    opt.value = value;
    opt.text = text;
    ddl.options.add(opt);
}

jackel414 · Accepted Answer · 2013-11-21 13:52:09Z

As suggested, AJAX was the answer. For anyone curious who comes across this, below is the solution I came up with. I left the HTML unchanged other than removing onchange="configureDropDownLists(this,'submitter_name')" from the first dropdown. Instead of the above Javascript, I used the below AJAX and PHP. Works really nicely.

JQuery:

$(document).ready(function() {
    $("#org_name").on("change", function() {
        var orgName = document.getElementById("org_name").value;
        $.post('admin_list.php', { org: orgName }, function(result) {
            $('#submitter_name').html(result);
            }
        );
    });
});

and the referenced PHP page:

<?php
    mysql_connect("database", "username", "password") or die(mysql_error ());
    mysql_select_db("databaseName") or die(mysql_error());
    $org_name = $_REQUEST['org'];
    $query = mysql_query("SELECT * FROM Table WHERE user = '$org_name'");

    while($row = mysql_fetch_array($query)){
        echo "<option>" . $row['admin_first_name'] . " " . $row['admin_last_name'] . "</option>";
    }    
    mysql_close();
?>

Crackertastic · Accepted Answer · 2013-10-23 03:11:33Z

1

Sounds like you need some AJAX to pull your data from the database, format on the server side (JSON will likely be easiest to work with), then use a callback function in Javascript to populate the second drop down based on the JSON data received.

answered Oct 23, 2013 at 3:11

Crackertastic

4,9232 gold badges33 silver badges40 bronze badges

2 Comments

jackel414 Over a year ago

I figured it might take something more than just PHP and Javascript. I'm not very familiar with AJAX/JSON at this point, but it gives me something to work with. Thanks.

Crackertastic Over a year ago

Glad to help. For what it is worth, jQuery has a pretty good AJAX library that can make life a little easier on you. It looks intimidating to work with at first. But do a couple small examples to get the hang of it and you should have your solution in no time.

Collectives™ on Stack Overflow

Pull dropdown Options from a DB based on selection in another dropdown

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related