Filtering dropdown based on another dropdown selection

Question

I have 2 separate dropdown lists. I need to get each dropdown to filter each other. Every example I have seen so far is an example for dropdowns that have the options hard-coded in. Mine uses a query to populate the options.

So how could I correctly have each dropdown menu filter each other?

Here is my HTML for the dropdowns on index.php:

<select id="collector" onchange="showUser(this.value)">             
    <option value="" selected disabled>Collector Name</option>
        <?php foreach($collect->fetchAll() as $name) { ?>
    <option class="<?php echo $name['Collector Name'];?>" value="<?php echo $name['Collector Name'];?>"><?php echo $name['Collector Name'];?></option>
        <?php } ?>
    </select>

<select id="date" onchange="showUser(this.value)">              
    <option value="" selected disabled>Bill Date</option>
        <?php foreach($bill_date->fetchAll() as $date) { ?>
    <option class="<?php echo $date['Date'];?>" value="<?php echo $date['Collector Name'];?>"><?php echo $date['Date'];?></option>
        <?php } ?>
    </select>

Code that runs each time the dropdown is changed in script tags on index.php:

function showUser(str) {
  if (window.XMLHttpRequest) {
            // code for IE7+, Firefox, Chrome, Opera, Safari
            xmlhttp = new XMLHttpRequest();
        }
    xmlhttp.onreadystatechange = function() {
        if (this.readyState == 4 && this.status == 200) {
            document.getElementById("txtHint").innerHTML = this.responseText;

            var newTableObject = document.getElementById('billing_table');
            sorttable.makeSortable(newTableObject);

        }
        }

    // ---- Gets value of collector dropdown selection -----
        var e = document.getElementById("collector").value;

        $.ajax({
         type: 'GET',
         url: 'index.php',
         data: e,
         success: function(response) {
             console.log(e);
         }
       });                  

    // ---- Gets value of the current selection in any of the dropdowns ----    
        xmlhttp.open("GET","dropdown-display.php?q="+str,true);
        xmlhttp.send();

        document.getElementById('billing_table').style.display = 'none';
    }

$(document).ready(function(){ 
    var $select1 = $( '#collector' ),
        $select2 = $( '#date' ),
    $options = $select2.find( 'option' );

$select1.on( 'change', function() {
    $select2.html( $options.filter( '[value="' + this.value + '"]' ) );
}).trigger( 'change' );


});

Query on my index.php page:

$collector = "SELECT [Collector Name]
  FROM [vSpecial_Billing]
  Group By [Collector Name]";

$billdate = "SELECT [Collector Name], [Date]
  FROM [vSpecial_Billing]
  Group By [Collector Name], [Date]";

I don't want to send the value to my dropdown-display.php page since my queries that populate the dropdowns are on my index.php page. However, if I put the value variable in the query, then it runs that query on load before a collector selection can be made and my bill date dropdown will then not be populated.

EDIT:

• I changed the value in the options for the date dropdown to Collector Name instead of Date
• I also added the $(document).ready(function() at the end of the middle block of code
• I updated the queries that I am using

It filters correctly now, however, on page load, the bill date is unable to selected. It is not populated with any rows. How can I change this?

Also, when I filter it, it defaults to the last date on the list. How can I get it to default to a hardcoded value such as "Date" and then the user can select from the filtered values?

Answer 1

I wrote up a test case, using some example data, and made sure this works. Its a rough example, but I believe its doing what you need. With a lot less cruft in the works. I'm sorry, but I used full jquery, because I cannot be bothered to do long-hand javascript anymore haha (plus I couldn't really follow what you had going on in there).

There will need to be two files: index.php and index-ajax.php (for clarity)

index.php brief:

// note: these do not need to be in prepared statements (theres no variables inside)
$collect  = $db->query("SELECT DISTINCT [Collector Name] FROM [vSpecial_Billing]");
$names    = $collect->fetchAll();
$billdate = $db->query("SELECT DISTINCT [Date] FROM [vSpecial_Billing]");
$dates    = $billdate->fetchAll();
?>

<form id="testForm" action="">
    <select id="collector">             
        <option value="" selected="selected" disabled="disabled">Collector Name</option>
        <?php foreach($names as $name) { ?>
            <option class="choice" value="<?php echo htmlspecialchars($name['Collector Name']);?>"><?php echo $name['Collector Name'];?></option>
        <?php } ?>
    </select>
    <select id="date">              
        <option value="" selected="selected" disabled="disabled">Bill Date</option>
        <?php foreach($dates as $date) { ?>
            <option class="choice" value="<?php echo $date['Date'];?>"><?php echo $date['Date'];?></option>
        <?php } ?>
    </select>
    <input type="button" id="clearchoices" name="clearchoices" value="Clear Choices" />
</form>

Some things to note in the above:

• You only need to select by DISTINCT. No need to do GROUP BY to get all unique names, or all unique dates.
• I put the results of fetchAll into variables, out of habit, but you can move them into the foreach if you wish.
• I removed the class defines you had, because a class with spaces in it (in the case of a Collector Name) can be buggy.
• The Clear Choices button is just an example of how to reset those selects after they get filtered and filtered beyond what you can select.

This is the javascript portion (it goes in index.php before or after your form, or in the head):

<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script language="Javascript" type="text/javascript">
$(document).ready(function(){

    $("#collector").change(function(e){
        $.post('index-ajax.php',{filter:'Name',by:$(this).val()},function(data){
            $("#date .choice").hide();
            $.each(data, function(key,row) {
       //       $("#date option[value='"+ row.item +"']").show();
                $("#date option").filter(function(i){
                    return $(this).attr("value").indexOf( row.item ) != -1;
                }).show();
            });
        },"JSON");
    });
    $("#date").change(function(e){
        $.post('index-ajax.php',{filter:'Date',by:$(this).val()},function(data){
            $("#collector .choice").hide();
            $.each(data, function(key,row) {
        //      $("#collector option[value='"+ row.item +"']").show();
                $("#collector option").filter(function(i){
                    return $(this).attr("value").indexOf( row.item ) != -1;
                }).show();
            });
        },"JSON");
    });
    $("#clearchoices").click(function(e){ e.preventDefault();
        $("#collector .choice").show(); $("#collector").val('');
        $("#date .choice").show(); $("#date").val('');
    });

});
</script>

That block needs a lot of explaining, because I took all your long-hand javascript and packed it into jquery.

• Each select has its own handler event for when it changes.
• Each select does its own post ajax, with a different variable define to filter on.
• After the ajax returns, it hides all options in the OTHER select. Then enables all options which are returned by the json data of the ajax call. This could be handled differently, but I wanted to present one way of doing it.
• A key thing is setting "JSON" for the return handler of the .post() methods. You'll see why in index-ajax.php.

And now the index-ajax.php:

if (isset($_POST['filter']) and isset($_POST['by'])) {// sanity check

    $results = array();

    if (!empty($_POST['by'])) {
        // these _DO_ need to be in prepared statements!!!
        if ($_POST['filter'] == 'Name') { $sql = "SELECT DISTINCT [Date] as item FROM [vSpecial_Billing] WHERE [Collector Name] = ?"; }
        if ($_POST['filter'] == 'Date') { $sql = "SELECT DISTINCT [Collector Name] as item FROM [vSpecial_Billing] WHERE [Date] = ?"; }
        $stmt = $db->prepare($sql);
        $stmt->execute(array($_POST['by']));
        while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) { $results[] = $row; }
    }

    echo json_encode( $results );
    exit;
}

This bit of code is actually pretty straightforward. All it does is determine which filter operation to do, prepares the sql, and then grabs distinct matching rows for output. The key thing though is it outputs as json, so the javascript that called this can handle the data easier!

Now... I had built all this in a test script, and my server hates "fetchAll", so your milage may vary on some of the DB code. I also left out all other form code and db setup handlers and all that. Figuring you have a handle on that.

I hope this helps you out, in some way or other.

EDIT 11/7

I made a slight change because I didn't realize the Collector Names in your db would have characters that would break all of this, oops. Two changes for odd character handling:

• The select for collector has its option values wrapped in htmlspecialchars().
• The jquery portion for where each select .change event filters, is now filtering by looking for a matching index, using the row.item as a direct variable. Before, it was using it in a value=' row.item ' match, which if the row.item had single quotes (or other bad chars), it would break the whole js event and fail!

Generally when I setup things like this, I use ID's and unique element id tags. That way I am only ever referencing by numbers, and wont run into odd character mash. An example of switching everything to ID's would be involved, and I think you have the gist of whats going on now.