問題描述
我有 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
valuein 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
classdefines 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 inindex-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
selectforcollectorhas itsoptionvalues wrapped inhtmlspecialchars(). - The
jqueryportion for where eachselect.changeevent filters, is now filtering by looking for a matching index, using therow.itemas a direct variable. Before, it was using it in avalue=' row.item 'match, which if therow.itemhad 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.
這篇關于基于另一個下拉選擇過濾下拉的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！