如何在DataTable中使用单独的列搜索和服务器端处理？

Question

我正在处理一个有大量数据的项目。我已经使用服务器端处理来快速处理或加载数据。但是我的老板想在每一列中添加搜索，我尝试使用多滤波器，但是数据没有改变。我该怎么解决这个问题？

这是从数据库加载数据的表：

<table class="table table-bordered responsive nowrap" id="example" width="100%">    
    <thead> 
        <tr>
            <th width="3%">No</th>
            <th width="30%">Machine</th>
            <th width="15%">Spec1</th>
            <th width="15%">Spec2</th>
            <th width="6%">Spec3</th>
            <th width="6%">Spec4</th>
            <th width="6%">Spec5</th>
            <th width="6%">Spec6</th>
            <th width="6%">Qty</th>
            <th></th>
        </tr>
    </thead>
    <tfoot>
        <tr>
            <th width="3%">No</th>
            <th width="30%">Machine</th>
            <th width="15%">Spec1</th>
            <th width="15%">Spec2</th>
            <th width="6%">Spec3</th>
            <th width="6%">Spec4</th>
            <th width="6%">Spec5</th>
            <th width="6%">Spec6</th>
            <th width="6%">Qty</th>
            <th></th>
        </tr>
    </tfoot>
</table>

这是我使用PHP从MySQL加载数据的脚本。我已经在ajax之后添加了多个过滤器脚本，但仍然无法工作。

$(document).ready(function(){
  //Reading
    var dataTable=$('#example').DataTable({
        "processing": true,
        "serverSide":true,
        "ajax":{
            url:"pages_exe/machine_dt.php",
            type:"POST"
        },
        initComplete: function () {
            this.api().columns().every( function () {
                var column = this;
                var select = $('<select><option value=""></option></select>')
                    .appendTo( $(column.footer()).empty() )
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );
 
                        column
                            .search( val ? '^'+val+'$' : '', true, false )
                            .draw();
                    } );
 
                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' );
                } );
            } );
        }
    });
});

这是PHP文件，可以从我的数据库中加载或获取数据。machine_dt.php

<?php
include '../environment.php';
include '../config/database.php';

$request=$_REQUEST;
$col =array(
    0   =>  'machid',
    1   =>  'machine',
    2   =>  'spec1',
    3   =>  'spec2',
    4   =>  'spec3',
    5   =>  'spec4',
    6   =>  'spec5',
    7   =>  'spec6',
    8   =>  'qty'
);  //create column like table in database

$sql =" SELECT * FROM machinelist
        /*WHERE qty != 'disable'*/
        ORDER BY machine ASC;";
$query=mysqli_query($db,$sql);

$totalData=mysqli_num_rows($query);

$totalFilter=$totalData;

//Search
$sql =" SELECT * FROM machinelist
        /*WHERE qty != 'disable'*/
        WHERE 1=1
        ";

if(!empty($request['search']['value'])){
    $sql.=" AND (machine Like '%".$request['search']['value']."%' ";
    $sql.=" OR brand Like '%".$request['search']['value']."%' ";
    $sql.=" OR spec2 Like '%".$request['search']['value']."%' ";
    $sql.=" OR spec3 Like '%".$request['search']['value']."%' ";
    $sql.=" OR spec4 Like '%".$request['search']['value']."%'";
    $sql.=" OR spec5 Like '%".$request['search']['value']."%' ";
    $sql.=" OR spec6 Like '%".$request['search']['value']."%' ";
    $sql.=" OR qty Like '%".$request['search']['value']."%' )";
}
$query=mysqli_query($db,$sql);
$totalData=mysqli_num_rows($query);

//Order
$sql.=" ORDER BY ".$col[$request['order'][0]['column']]."   ".$request['order'][0]['dir']."  LIMIT ".
    $request['start']."  ,".$request['length']."  ";

$query=mysqli_query($db,$sql);

$data=array();
$i=1;
while($row=mysqli_fetch_array($query)){
    $subdata=array();

    $subdata[]= $i++;
    $subdata[]=$row[1]; 
    $subdata[]=$row[2]; 
    $subdata[]=$row[3]; 
    $subdata[]=$row[4]; 
    $subdata[]=$row[5];  
    $subdata[]=$row[6]; 
    $subdata[]=$row[7];
    $subdata[]=$row[8];
    $subdata[]='<button type="button" class="btn btn-primary btn-sm" id="update" data-id="'.$row[0].'" >Edit</button>
                <button type="button" class="btn btn-danger btn-sm" id="delete" data-id="'.$row[0].'" >Delete</button>';
    $data[]=$subdata;
}

$json_data=array(
    "draw"              =>  intval($request['draw']),
    "recordsTotal"      =>  intval($totalData),
    "recordsFiltered"   =>  intval($totalFilter),
    "data"              =>  $data
);

echo json_encode($json_data);

?>

Answer 1

$sql.=" AND (machine Like '%".$request['search']['value']."%' ";
$sql.=" OR brand Like '%".$request['search']['value']."%' ";
$sql.=" OR spec2 Like '%".$request['search']['value']."%' ";
$sql.=" OR spec3 Like '%".$request['search']['value']."%' ";
$sql.=" OR spec4 Like '%".$request['search']['value']."%'";
$sql.=" OR spec5 Like '%".$request['search']['value']."%' ";
$sql.=" OR spec6 Like '%".$request['search']['value']."%' ";
$sql.=" OR qty Like '%".$request['search']['value']."%' )";

玩具项目没问题。但它的规模不会超过一千行。

如果你的老板在规模上需要灵活性和绩效，那么你需要把这个问题转变成更类似的问题。

“如何有效地对多列进行文本搜索？”

对于这个问题，您将得到一个讨论全文索引，并将这些列合并成一个列。