基于另一个下拉选择过滤下拉-php问题

Filtering dropdown based on another dropdown selection(基于另一个下拉选择过滤下拉)

本文介绍了基于另一个下拉选择过滤下拉的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有 2 个单独的下拉列表.我需要让每个下拉菜单相互过滤.到目前为止，我所看到的每个示例都是一个带有硬编码选项的下拉列表示例.我的使用查询来填充选项.

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?

这是我用于 index.php 下拉菜单的 HTML:

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>

每次在 index.php 上的 script 标记中更改下拉列表时运行的代码:

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' );


});

在我的 index.php 页面上查询:

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]";

我不想将值发送到我的 dropdown-display.php 页面，因为填充下拉列表的查询在我的 index.php 页面上.但是，如果我将 value 变量放在查询中，那么它会在加载时运行该查询，然后才能进行收集器选择，并且不会填充我的帐单日期下拉列表.

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.

我将日期下拉选项中的 value 更改为 Collector Name 而不是 Date
我还在中间代码块的末尾添加了$(document).ready(function()
我更新了我正在使用的查询

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?

推荐答案

我使用一些示例数据编写了一个测试用例，并确保它有效.这是一个粗略的例子，但我相信它可以满足您的需求.在工作中少了很多麻烦.我很抱歉，但我使用了完整的 jquery，因为我不能再费心做长手 javascript 了哈哈(而且我无法真正理解你在那里发生的事情).

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).

需要两个文件:index.php 和 index-ajax.php(为了清晰起见)

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

index.php 简介:

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>

以上几点需要注意:

您只需按 DISTINCT 进行选择.无需执行 GROUP BY 即可获取所有唯一名称或所有唯一日期.
出于习惯，我将 fetchAll 的结果放入变量中，但如果您愿意，您可以将它们移动到 foreach 中.
我删除了您拥有的 class 定义，因为其中包含空格的类(在收集器名称的情况下)可能有问题.
清除选择"按钮只是一个示例，说明如何在筛选和筛选超出您可以选择的范围后重置这些选择.

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.

这是 javascript 部分(它在表单之前或之后的 index.php 中，或在头部):

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>

那个块需要很多解释，因为我把你所有的长手javascript都打包到jquery中.

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

每个选择都有自己的处理程序事件，用于更改时.
每个选择都执行自己的 post ajax，并使用不同的变量定义进行过滤.
ajax 返回后，它会隐藏 OTHER 选择中的所有选项.然后启用 ajax 调用的 json 数据返回的所有选项.这可以有不同的处理方式，但我想介绍一种方法.
关键是为 .post() 方法的返回处理程序设置 "JSON".您将在 index-ajax.php 中看到原因.

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.

现在是 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;
}

这段代码实际上非常简单.它所做的只是确定要执行哪个过滤器操作，准备 sql，然后抓取不同的匹配行进行输出.但关键是它输出为 json，因此调用它的 javascript 可以更轻松地处理数据！

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!

现在...我已经在测试脚本中构建了所有这些，而我的服务器讨厌fetchAll"，因此您的里程可能会因某些数据库代码而异.我还省略了所有其他表单代码和数据库设置处理程序等等.认为您对此有所了解.

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.

编辑 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:

collector 的 select 的 option 值包含在 htmlspecialchars() 中.
用于每个 select .change 事件过滤器的 jquery 部分现在通过查找匹配索引进行过滤，使用 row.item 作为直接变量.之前，它在 value=' row.item ' 匹配中使用它，如果 row.item 有单引号(或其他坏字符)，它会中断整个 js 事件并失败！

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!

一般来说，当我设置这样的东西时，我会使用 ID 和唯一元素 ID 标签.这样我只用数字引用，不会遇到奇怪的字符混搭.将涉及将所有内容都切换为 ID 的示例，我认为您已经了解了现在发生的事情的要点.

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.

这篇关于基于另一个下拉选择过滤下拉的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持编程学习网！