Filter HTML Dropdown Based on Another Dropdown Selection

Question

I have multiple HTML dropdowns. After one selection, I want it to automatically populate the next dropdown. All of this information is being brought in to populate the lists from a database using a SQL statement and foreach loop, so I cannot hard code the values like all of the examples out there related to my question. I currently have just a bit of JavaScript for this as of now although I am not sure if I am going in the right direction. I am thinking that this will need to involve some AJAX and an onChange listener. I am just unsure of how to get started.

So how can I do this? I am not asking for you to do this for me, but just some code (like an outline) to give me a head start and get me going would be appreciated! Thank you!

SQL Statements:

<?php
$host="xxxxxxx"; 
$dbName="xxxx"; 
$dbUser="xxxxxxxxxxxxxx"; 
$dbPass="xxxxxxxxxxx";


$dbh = new PDO( "sqlsrv:server=".$host."; Database=".$dbName, $dbUser, $dbPass);
$dbh->setAttribute( PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION );
$sql_major = "SELECT DISTINCT [Major Category] FROM vProducts ORDER BY [Major Category] ASC";
$sql_minor = "SELECT DISTINCT [Minor Category] FROM vProducts ORDER BY [Minor Category] ASC";
$sql_code = "SELECT DISTINCT [Product Report Code] FROM vProducts ORDER BY [Product Report Code] ASC";

$dropdown_major = $dbh->query($sql_major);
$dropdown_minor = $dbh->query($sql_minor);
$dropdown_code = $dbh->query($sql_code);
?>

Dropdowns:

<table cellspacing="5" align="center" id="dropdown-table">
    <thead>
      <tr>
        <th>Major Category</th>
        <th>Minor Category</th>
        <th>Report Code</th>
        <th>SKU</th>
      </tr>
    </thead>
    <tbody> 
        <tr>
            <td>


                <select id="major" onChange="updateCat();">
                <option value="" disabled="disabled" selected="selected">Please Select One</option>
    <?php foreach ($dropdown_major->fetchAll() as $drop_major): ?>
                <option
                    value=""
                    data-name="<?php echo $drop_major ['Major Category'];?>"
                >
                    <?php echo $drop_major ['Major Category'];?>
                </option>
        <?php endforeach; ?>
                </select>
            </td>


            <td>
                <select id="minor">
                <option value="" disabled="disabled" selected="selected">Please Select One</option>
    <?php foreach ($dropdown_minor->fetchAll() as $drop_minor): ?>
                    <option
                        value=""
                        data-name="<?php echo $drop_minor ['Minor Category'];?>"
                    >
                        <?php echo $drop_minor ['Minor Category'];?>
                    </option>
        <?php endforeach; ?>
                </select>
            </td>


            <td>
                <select>
                <option value="" disabled="disabled" selected="selected">Please Select One</option>
    <?php foreach ($dropdown_code->fetchAll() as $drop_code): ?>
                    <option
                        value="code"
                        data-name="<?php echo $drop_code ['Product Report Code'];?>"
                    >
                        <?php echo $drop_code ['Product Report Code'];?>
                    </option>
        <?php endforeach; ?>
                </select>
            </td>


            <td>
                <select>
                <option value="" disabled="disabled" selected="selected">Please Select One</option>
                    <option value="sku">SKU</option>
                </select>
            </td>
            <td><input type="button" value="Search" id="searchButton" onclick="show();"></td>
            <td><button class="create-user" id="insertButton">Add Group</button></td>
        </tr>
    </tbody>
</table>

JavaScript:

// JS for Dropdown

function updateCat() {
    var e = document.getElementById("major");
    var majorSelected = e.options[e.selectedIndex];

    document.getElementById("minor").value = majorSelected.dataset.name;
}

Answer 1

As a starting point I would suggest looking at the W3schools section on AJAX PHP.

Using an AJAX PHP one approach to this would be to leverage the DOM onchange event of your dropdown to pass the selected value through to a PHP page via XMLHttpRequest in a javascript function.

The PHP page could then be set up to return the second list of values from the database. The returned data could be the elements for your second dropdown.

Looking at your code I would perform the first SQL query to generate the major category items in the main page where your table resides. The second SQL query for your minor category items would then reside in your second page where the AJAX call is made. The second query will only be invoked then when the value within your dropdowns are changed.

UPDATE 1: Adding some code

1. Major Items Select Box

Run your SQL query to generate your values for the major options.

<select id="majoritems" onChange="updateCat();">
<option value="" disabled="disabled" selected="selected">Please Select One</option>
<?php echo $major_options; ?>

2. Minor Items Select Box

<select id="minoritems">
 <option value="" disabled="disabled" selected="selected">Please Select One</option>

3. Javascript AJAX Call

The following draws on the W3Schools AJAX PHP example primarily.

<script>function updateCat(val){
if (val.length == 0) { 
    document.getElementById("minoritems").innerHTML = "";
    return;
} else {
    var xmlhttp = new XMLHttpRequest();
    xmlhttp.onreadystatechange = function() {
        if (this.readyState == 4 && this.status == 200) {
            document.getElementById("minoritems").innerHTML = this.responseText;
        }
    };
    xmlhttp.open("GET", "getminoritems.php?q=" + val, true);
    xmlhttp.send();
}}</script>

When the getminoritems.php page is called the URL parameters are set which will send the val variable across as a GET variable. This can then be used to query the items in the database based on that value.

4. Your PHP page to return the list of minor options based on the value sent in the AJAX GET call

In your PHP page you should now be able to access the $_GET variable q which was set in the AJAX call with the value of val. Taking this you can query your database, e.g.

$val = $_GET['q'];

$sql_minor = "SELECT DISTINCT [Minor Category] FROM vProducts WHERE category = $val ORDER BY [Minor Category] ASC";

etc etc...your code to return values

Make sure to return the values within <option>tags in the end. They will be output within in the minoritems select element as the javascript function uses the .innerhtml to set it to the response text received from your AJAX call.

Hope this helps! PS: I would suggest doing a very very simple example using the W3schools tutorial if you can so that you can get your head fully around what is happening here.