javascript — Фильтрация отдельных столбцов на стороне сервера DataTables дает только первые варианты выбора

Я провел некоторое исследование по этому вопросу и не смог найти пример кода, поэтому я нашел решение, которое работает довольно хорошо. Чтобы объяснить, как это работает, я создал функцию, которая выполняет вызов ajax, когда выпадающий список выбора фокусируется на. Также я добавил предложение where в php datatables для обработки отображения отфильтрованных данных json.

Важно отметить, что мне пришлось установить управление потоком, поскольку при фильтрации по столбцам происходит странное поведение при использовании других элементов tabletools. Мое решение состояло в том, чтобы очистить фильтрацию, выбранную при использовании табличного инструмента, чтобы было прослушано множество событий.

HTML

    <script type="text/javascript" language="javascript" src="./jquery-1.11.1.min.js"></script>
<script type="text/javascript" language="javascript" src="./jquery.dataTables.min.js"></script>
<script type="text/javascript" language="javascript" src="./dataTables.tableTools.min.js"></script>
<script type="text/javascript" language="javascript" class="init">

var where;
var selInitd = new Array();
var selectedData;

$(document).ready(function() {
function getSelectedList(str){
var response = new Array();
var returnArray = new Array();
$.ajax({
url: "./filter.php",
data: "selected="+str,
async: false,
success: function(msg){
response = msg.split(',');
//alert(response);
for(m = 0; m < response.length; m++){
response[m] = response[m].replace('[','');
response[m] = response[m].replace(']','');
response[m] = response[m].replace(/\"/g,'');

var val = response[m];
returnArray.push(val);
}
}
})

return returnArray;
}

var columnNumber = $('#example thead th').length;

//this can probably be handled in PHP
function colSelected(column){
switch(column){
case 0:
return "columnA";
break;
case 1:
return "columnB";
break;
case 2:
return "columnC";
break;
case 3:
return "columnD";
break;
case 4:
return "columnE";
break;
case 5:
return "columnF";
break;
case 6:
return "columnG";
break;
case 7:
return "columnH";
break;
case 8:
return "columnI";
break;
case 9:
return "columnJ";
break;
default:
return "columnA";
}
}

var table = $('#example')
.DataTable( {
scrollX: true,
dom: '<"top"fl>rt<"bottom"ipT><"clear">',
serverSide: true,
processing: true,
ordering: true,
order: [[ 9, "columnJ" ]],
ajax: {
url: "../dt.php",
data: function ( json ){
json.where = where;
},
},
tableTools: {
sRowSelect: "os",
sSwfPath: "./copy_csv_xls_pdf.swf",
aButtons: [
{
sExtends: "collection",
sButtonText: "Save",
sButtonClass: "save-collection",
aButtons: [ 'copy', 'csv', 'xls', 'pdf' ]
},
'print'
]
},
initComplete: function (settings, json) {
var api = this.api();
//alert(Object.keys(settings.oPreviousSearch));

var col;

api.columns().indexes().flatten().each( function ( i ) {
selInitd[i] = false;
var column = api.column( i );
//made the column number the id for getElementById
var select = $('<select id=' + i + '><option value=""></option></select>')
.appendTo( $(column.footer()).empty() )
.on( 'change', function () {
for (k = 0; k < columnNumber; k++){
//inefficient but gets every column selected every time any column is selected
var value = document.getElementById(k).value;
if (value != "" ){

//exposes the column names, you probably want to do this differently
col = colSelected(k);

//build where to send to php, you probably want to do this differently
if (where == null){
where = col + " = '" + value + "'";
}else{
where = where + " AND " + col + " = '" + value + "'";
}
}
}

api.ajax.reload( null, true );

where = null;

} )
.on( 'focus', function () {
if(!selInitd[i]){
var selectedL = getSelectedList(colSelected(i)).slice();

for(d = 0; d < selectedL.length; d++) {
select.append( '<option value="' + selectedL[d] + '">' + selectedL[d] + '</option>' )
}

selInitd[i] = true;
}
} );
} );
}
} );

//reset filter selections when text is entered in the search box
$(".dataTables_filter input")
.on("input.dt", function(e) {
//reset select inputs
for(h = 0; h < columnNumber; h++){
var value = document.getElementById(h);
value.selectedIndex = 0;
}

return;
});

//reset filter selections when sorting by column
$(".dataTables_scrollHeadInner th.sorting")
.on("click.dt", function(e) {
//reset select inputs
for(h = 0; h < columnNumber; h++){
var value = document.getElementById(h);
value.selectedIndex = 0;
}

return;
});

//reset filter selections page length widget is changed
$(".dataTables_length select")
.on("click.dt", function(e) {
//reset select inputs
for(h = 0; h < columnNumber; h++){
var value = document.getElementById(h);
value.selectedIndex = 0;
}

return;
});

//reset filter selections page number is changed
table.on("page.dt", function(e) {
//reset select inputs
for(h = 0; h < columnNumber; h++){
var value = document.getElementById(h);
value.selectedIndex = 0;
}

return;
});
} );

</script>
</head>

</body>
<div class="container">
<table id="example" class="display" cellspacing="0" width="100%">
<thead>
<tr>
<th>columnA</th>
<th>columnB</th>
<th>columnC</th>
<th>columnD</th>
<th>columnE</th>
<th>columnF</th>
<th>columnG</th>
<th>columnH</th>
<th>columnI</th>
<th>columnJ</th>
</tr>
</thead>
<tfoot>
<tr>
<th>columnA</th>
<th>columnB</th>
<th>columnC</th>
<th>columnD</th>
<th>columnE</th>
<th>columnF</th>
<th>columnG</th>
<th>columnH</th>
<th>columnI</th>
<th>columnJ</th>
</tr>
</tfoot>
</table>
</div>
</body>
</html>

Фильтр PHP

<?php
$column = $_GET['selected'];

//conection:
$link = mysqli_connect("ip.address","user","password","DB") or die("Error " . mysqli_error($link));

//consultation:

$query = "SELECT DISTINCT ".$column." FROM table ORDER BY ".$column." ASC" or die("Error in the consult.." . mysqli_error($link));

//execute the query.

$result = $link->query($query);

//display information:

$rows = array();
$rIdx = 0;

while($row = mysqli_fetch_array($result)) {
$rows[$rIdx] =  $row[$column];
$rIdx++;
}

if($rows){
echo json_encode($rows);
}

DT PHP

<?php

/*
* DataTables example server-side processing script.
*
* Please note that this script is intentionally extremely simply to show how
* server-side processing can be implemented, and probably shouldn't be used as
* the basis for a large complex system. It is suitable for simple use cases as
* for learning.
*
* See http://datatables.net/usage/server-side for full details on the server-
* side processing requirements of DataTables.
*
* @license MIT - http://datatables.net/license_mit
*/

/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

// DB table to use
$table = 'table';

// Table's primary key
$primaryKey = 'columnA';

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database, while the `dt`
// parameter represents the DataTables column identifier. In this case simple
// indexes
$columns = array(
array( 'db' => 'columnA',   'dt' => 0 ),
array( 'db' => 'columnB',   'dt' => 1 ),
array( 'db' => 'columnC',   'dt' => 2 ),
array( 'db' => 'columnD',   'dt' => 3 ),
array( 'db' => 'columnE',   'dt' => 4 ),
array( 'db' => 'columnF',   'dt' => 5 ),
array( 'db' => 'columnG',   'dt' => 6 ),
array( 'db' => 'columnH',   'dt' => 7 ),
array( 'db' => 'columnI',   'dt' => 8 ),
array( 'db' => 'columnJ',   'dt' => 9 )
);

// SQL server connection information
$sql_details = array(
'user' => 'user',
'pass' => 'password',
'db'   => 'DB',
'host' => 'ip.address'
);

$whereAllClause = '';

if ($_GET['where'] != ''){
$whereAllClause = $_GET['where'];
$whereAllClause = str_replace('+', ' ', $whereAllClause);
$whereAllClause = str_replace('%3D', '=', $whereAllClause);
$whereAllClause = str_replace('%27', '\'', $whereAllClause);
}/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP
* server-side, there is no need to edit below this line.
*/

require( './ssp.class.php' );

echo json_encode(
SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, $whereAllClause )
);