Multiple column server side filtering

beachcm65

Trying to switch from client to server side scripting - having trouble getting my multiple filters to work. Actually, I can't get one filter to work.

The code I have seems to work on the initial load, but changing the select filter has no effect. Here is my code, which you can find here: https://re.templeton.org/humility_poc/reconciliation_test1.html

  <script type="text/javascript">
  $(document).ready(function() {
      $('#pub_table').DataTable( {
        caseInsensitive: true,
        dom: 'lrtipB',
        processing: true,
        serverSide: true,
        ajax: {
          url: "reconcile_output_data.php",
          "type": "GET",
          "data": function(d) {
                 return $.extend( {}, d, {
                    "pub_table_col6_filter": $('#pub_table_col6_filter').val()
                  } );
          }
        },
        buttons: ['copy', 'excel'],
        scrollY:        "300px",
        scrollX:        true,
        scrollCollapse: true,
        paging:         false,
        fixedColumns:   {
            heightMatch: 'none'
        },
        "order": [[ 1, "desc" ]],
        "aoColumns": [
            { "orderSequence": [ "desc", "asc" ] },
            { "orderSequence": [ "desc", "asc" ] },
            { "orderSequence": [ "desc", "asc" ] },
            { "orderSequence": [ "desc", "asc" ] },
            { "orderSequence": [ "desc", "asc" ] },
            { "orderSequence": [ "desc", "asc" ] },
            { "orderSequence": [ "desc", "asc" ] },
            null,
            null,
        ],
        "columnDefs": [
            { targets: [7,8], visible: false},
            { targets: '_all', visible: true}
        ]

      } );

  });

    function filterColumn ( i ) {
//      $('#' + field_input_table).DataTable().column( i ).search($('#' + field_input_table + '_col'+i+'_filter').val(), 'on', 'on').draw();
      $('#pub_table').DataTable().draw();
    }

    $(document).ready(function() {
      $(".column_filter").change(function() {
        field_input_table = $(this).parents('div').attr('datatable');
        filterColumn( $(this).attr('col_filter_num') );
      } );
    } );

  </script>


<div id="pubfilterpanel" datatable="pub_table">
<table style="width:100%" >
<tbody>

<tr>
<td>Id:</td>
<td><input type="text" class="column_filter" id="pub_table_col0_filter" col_filter_num=0></td>
<td>&nbsp;</td>
<td>Project</td>
<td><select class="column_filter" id="pub_table_col8_filter" col_filter_num=8>
<option value="">All
<option value="^Humility Final">Humility Final
</select></td>
<td>&nbsp;</td>
<td>Year:</td>
<td><input type="text" id="pub_table_year_min" class="column_filter_minmax" size="2"> Min <input type="text" id="pub_table_year_max" class="column_filter_minmax" size="2"> Max</td>
</tr>

<tr>
<td>Publication Name:</td>
<td><input type="text" class="column_filter" id="pub_table_col2_filter" col_filter_num=2></td>
<td>&nbsp;</td>
<td>Agreement?</td>
<td><select class="column_filter" id="pub_table_col5_filter" col_filter_num=5>
<option value="">All
<option value="Yes">Yes
<option value="No">No
</select></td>
<td>&nbsp;</td>
<td>Number of Reviews</td>
<td><input type="text" id="pub_table_rev_min" class="column_filter_minmax" size="2"> Min <input type="text" id="pub_table_rev_max" class="column_filter_minmax" size="2"> Max</td>
</tr>

<tr>
<td>Publication Title:</td>
<td><input type="text" class="column_filter" id="pub_table_col3_filter" col_filter_num=3></td>
<td>&nbsp;</td>
<td>Final</td>
<td><select class="column_filter" id="pub_table_col6_filter" col_filter_num=6>
<option value="">All
<option value="Yes" selected>Yes
<option value="No">No
</select></td>
</tr>

<tr>
<td>Reviewer Name</td>
<td><input type="text" class="column_filter" id="pub_table_col7_filter" col_filter_num=7></td>
<td>&nbsp;</td>
</tr>


</tbody></table>
</div>
<p>

<table id="pub_table" class="stripe row-border order-column" style="width:100%" border=1 cellpadding=2 cellspacing=0>
 <thead>
  <tr>
    <th align=left>Id</th>
    <th align=left>Year</th>
    <th align=left>Name</th>
    <th align=left>Title</th>
    <th align=center>#Reviews</th>
    <th align=center>Agree?</th>
    <th align=center>Final?</th>
    <th align=center>Reviewers</th>
    <th align=center>Project</th>
   </tr>
 </thead>

</table>

kthorngren

Does your server script support the server side processing protocol documented here?
https://datatables.net/manual/server-side

Your server script is now responsible for all the searching and sorting functions of the table.

Kevin

beachcm65

Hi Kevin,

Still learning this, but I think so. Your question made me wonder whether the server script was being called after I change the dropdown variable.

I found that the script IS being called and outputting the correct JSON data set, but for some reason the data set isn't making its way back to the client-side.

Thanks for any ideas on what I'm missing.

beachcm65

Anyone?

kthorngren

What are you using for your server side script? Is it one of the scripts provided by Datatables?

Is the server script generating errors?

Kevin

beachcm65

Hi Kevin,

Right now i just have my data stored in a JSON file, not a database, so I wrote a php script that creates and echoes a JSON object based on the filter input. I've got no errors - the script is echoing the correct JSON file. Maybe there is something wrong with it though?

$count=0;
$total=0;
foreach (array_keys($rpubs) as $pub_id) {
  $data["data"][$count][0]="<a href=\"/humility_poc/reconcile_pub.php\" target=_blank>$pub_id</a>";
  $data["data"][$count][1]=$rpubs[$pub_id]["year"];
  $data["data"][$count][2]=$rpubs[$pub_id]["name"];
  $data["data"][$count][3]=$rpubs[$pub_id]["title"];
  $data["data"][$count][4]=$rpubs[$pub_id]["reviews"];
  $data["data"][$count][5]=($rpubs[$pub_id]["agree"]==0 ? 'No' : 'Yes');
  $data["data"][$count][6]=(isset($freviews[$pub_id]) ? 'Yes' : 'No');
  $data["data"][$count][7]=implode(",",array_map("reviewer_find", array_keys($reviews[$pub_id])));
  $data["data"][$count][8]="Humility Final";
  $total++;
  if (isset($_GET["pub_table_col6_filter"])) {
    if ( ( ($_GET["pub_table_col6_filter"]=="Yes") && ($data["data"][$count][6]=="No") ) || ( ($_GET["pub_table_col6_filter"]=="No") && ($data["data"][$count][6]=="Yes") ) ) {
      unset($data["data"][$count]);
    } else {
      $count++;
    }
  }
}
$data["draw"]=1;
$data["recordsTotal"]=$total;
$data["recordsFiltered"]=$count;
echo json_encode($data);

//$f = fopen(testfile.txt', 'a');
//fwrite($f, date("Y-m-d h:i:sa") . "\n" . print_r($data, true));
//fclose($f);

kthorngren

$data["draw"]=1;

With server side processing this is used as a sequence number by Datatables. It should match the draw value sent to the script. If you always return 1 then I believe Datatables will ignore the response assuming it has already received this sequence.

Kevin

beachcm65

Ah, thank you, thank you Kevin!!

I misread what "Draw" was supposed to be - I appreciate you clearing that up. The server side scripting is so nice.