根据另一个下拉选项筛选下拉菜单 [英] Filtering dropdown based on another dropdown selection

查看：131
本文介绍了根据另一个下拉选项筛选下拉菜单的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

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



所以我怎么才能正确的每个下拉菜单过滤对方？



以下是我在 index.php 下拉列表的HTML：

 < select id =collectoronchange =showUser（this.value）> 
< option value =选择禁用>收藏家名称< / 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 =dateonchange =showUser（this.value）> 
< option value =选择禁用>账单日期< / 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>

每次在 script index.php ：


$ b $ $ 

 函数showUser（str ）{
 if（window.XMLHttpRequest）{
 //代码为IE7 +，Firefox，Chrome，Opera，Safari 
 xmlhttp = new XMLHttpRequest（）; （$ this.readyState == 4&& this.status == 200）{
 document.getElementById（ txtHint）。innerHTML = this.responseText; 
 
 var newTableObject = document.getElementById（'billing_table'）; 
 sorttable.makeSortable（newTableObject）; 
 
 
 
 
 ----获取收集器下拉选择的值----- 
 var e = document.getElementById（ 集电极）值。 
 
 $ .ajax（{
类型：'GET'，
 url：'index.php'，
数据：e，
成功：函数（响应）{
 console.log（e）; 
} 
}）; 
 
 // ----获取任何下拉列表中当前选择的值---- 
 xmlhttp.open（GET，dropdown-display.php？q = + STR，TRUE）; 
 xmlhttp.send（）; 
 
 document.getElementById（'billing_table'）。style.display ='none'; 
 
 $ b $（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 页面： 

  $ collector =SELECT [Collector Name] 
 FROM [vSpecial_Billing] 
 Group By [Collector Name ]; 
 
 $ billdate =SELECT [Collector Name]，[Date] 
 FROM [vSpecial_Billing] 
 Group By [Collector Name]，[Date];

我不想将值发送到我的下拉式显示中。 php 页面，因为填充下拉列表的查询位于我的 index.php 页面。但是，如果我将值变量放在查询中，那么它会在执行收集器选择之前运行该查询，并且我的账单日期下拉列表将不会被填充。



<编辑：




  • 我更改了值在日期下拉选择收集器名称而不是日期的选项中添加 $（document）.ready（function（）在中间代码块的最后
  • 我更新了我正在使用的查询



现在过滤正确，但是，在页面加载时，账单日期无法选择。它没有填充任何行。我该如何改变它？



另外，当我过滤它时，它默认为列表中的最后一个日期，我怎样才能使它默认为硬编码的值，如日期，然后用户可以从过滤的值中选择？

解决方案

我写了一个测试用例，使用了一些示例数据，并且ma确定这是有效的。它是一个粗略的例子，但我相信它正在做你所需要的。在作品中少了很多东西。我很抱歉，但我使用了完整的jquery，因为我不能再为长时间使用JavaScript而烦恼了（再加上我无法真正关注你在那里做了什么）。



需要两个文件： index.php 和 index-ajax.php （ ）

index.php简介： 

  // note ：这些不需要在准备好的语句中（这里面没有变量）
 $ 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 =testFormaction => 
< select id =collector> 
< option value =selected =selecteddisabled =disabled>收藏家名称< / option> 
<？php foreach（$ names as $ name）{？> 
< option class =choicevalue =<？php echo htmlspecialchars（$ name ['Collector Name']）;？>><？php echo $ name ['Collector Name' ];>< /选项> 
<？php}？> 
< / select> 
< select id =date> 
< option value =selected =selecteddisabled =disabled> Bill Date< / option> 
<？php foreach（$ date as $ date）{？> 
< option class =choicevalue =<？php echo $ date ['Date'];？>><？php echo $ date ['Date'];？> < /选项> 
<？php}？> 
< / select> 
< input type =buttonid =clearchoicesname =clearchoicesvalue =Clear Choices/> 
< / form>

上面提到了一些注意事项：


  • 您只需要按DISTINCT选择。无需进行GROUP BY即可获取所有唯一名称或所有唯一日期。

  • 我将fetchAll的结果放入变量中，出于习惯，但可以将它们移动到foreach中你希望。

  • 我删除了定义你的 class ，因为一个包含空格的类（在收集器名称）可能会出现错误。

  • 清除选项按钮只是一个例子，说明如何重置这些选择，然后将它们过滤并过滤到您可以选择的范围之外。



这是javascript部分（它在index.php在你的表单之前或之后，或在头部）：

 < script src =https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js>< /脚本> 
< script language =Javascripttype =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 +']） 。（）; 
 $（＃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。


  • 每个select在它发生更改时都有自己的处理程序事件。

  • 它自己的帖子ajax，用一个不同的变量定义过滤。

  • ajax返回后，它隐藏OTHER选择中的所有选项。然后启用ajax调用的json数据返回的所有选项。这可以用不同的方式处理，但我想介绍一种方法。

  • 关键是将JSON设置为 .post（）方法的返回处理程序。你会明白为什么在 index-ajax.php 。



现在index-ajax.php：

$ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $ $'通过']））{//完整性检查

$ result = array（）;

if（！empty（$ _ POST ['by']））{
//这些_DO_需要准备好语句！
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）;
出口;
}

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



现在...我已经在测试脚本中构建了所有这些，而我的服务器讨厌fetchAll，所以你的milage可能会因某些DB代码而异。我也忽略了所有其他表单代码和db安装处理程序等等。我希望这可以帮助你，以某种方式或其他方式。



编辑11/7



我做了一个小改动，因为我没有意识到数据库中的收集器名称会有会破坏的字符所有这一切，哎呀。奇数字符处理的两个变化：


  • 选择用于收藏夹在 htmlspecialchars（）中包含选项值。

  • 每个选择 。的 jquery 部分事件过滤器，现在通过查找匹配索引进行过滤，使用 row.item 作为直接变量。之前，它在 value ='row.item' match中使用它，如果 row.item 单引号（或其他不好的字符），它会打破整个js事件并失败！



通常当我设置这样的事情时，我使用ID和独特的元素ID标签。这种方式我只能用数字来引用，并且不会遇到奇怪的字符混搭。将所有内容都切换到ID的例子将会涉及到，我认为您现在有了什么要点。


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?

解决方案

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.

这篇关于根据另一个下拉选项筛选下拉菜单的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
PHP最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆