range search- filter in server-side generated table

Lerster

Hello,

I want to use the "Custom filtering - range search" in my ajax server-sided generated table.

This is the example-page:

https://datatables.net/examples/plug-ins/range_filtering.html

As you see in this test case it doesn´t work. The "salary" range search doesn´t react:

http://live.datatables.net/kadomira/1/edit

Thanks for help.

colin

When serverSide is enabled, all search (and ordering and paging) is performed by the script on the server, the client only displays the data returned. Because of that, your custom search won't be called, you'll need that functionality on the server.

Colin

Lerster

I´ve searched a lot but I can´t find an example how to do a range search server sided. Do you have an example for me so that I´ve a starting point?

colin

There are a few threads on the forum discussing this - such as here and here, and this SO thread,

Colin

Lerster

So I gave my best, but it´s a bit complicated.

This is my try. In the **function ( m ) {} **I try to send the maxid and minid to the getData.php

$(document).ready(function(){

// Initialize DataTables API object and configure table
var table = $('#example').DataTable({
"searching": true,
"processing": true,
"serverSide": true,
"ajax": {
   "url": "https://xxxy.de/getData.php",
   "type": "GET",
                 "data": function ( d ) {              
                                             return $.extend( {}, d, {"search_keywords": $("#searchInput").val(),                                 
                                                                      "filter_option": $("#sortBy").val(),
                                                                      "filter_option2": $("#sortBy2").val()
                                                                      } );
                                         },

                          function ( m ) {              
                                             m.minid = $('#minid').val(); 
                                             m.maxid = $('#maxid').val();

                                         },

            },
});


  // Redraw the table based on the search/ filter input
 $('#searchInput,#sortBy,#sortBy2').bind("keyup change", function(){
    table.draw();
 });

 // Redraw the table based on the custom range variables
   $('#minid, #maxid').keyup( function() {
 table.draw();
  } );

});

I think I need to catch these variables now in the getData.php in an array and make a mysql query to my database with the condition.

Since I want to be able to range sort the column "id" of my table "item_classes" it would look something like

$sql = "SELECT id FROM item_classes BETWEEN $minid AND $maxid";

But I don´t know how to handle it in the getData.php

 <?php

   // Database connection info
   $dbDetails = array(
   'host' => 'localhost',
   'user' => 'xx',
   'pass' => 'xx',
   'db'   => 'xx'
 );

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

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

// Array of database columns which should be read and sent back to DataTables.
// The `db` parameter represents the column name in the database.
// The `dt` parameter represents the DataTables column identifier.
 $columns = array(
 array( 'db' => 'id', 'dt' => 0 ),
 array( 'db' => 'name', 'dt' => 1 ),

);



   $searchFilter = array();
   if(!empty($_GET['search_keywords'])){
   $searchFilter['search'] = array(
    'name' => $_GET['search_keywords'],
   );
 }
   if(!empty($_GET['filter_option'])){
    $searchFilter['filter'] = array(
    'id' => $_GET['filter_option'],
    );
}

      if(!empty($_GET['filter_option2'])){
       $searchFilter['filter'] = array(
        'id' => $_GET['filter_option2'],
    );
}

  // Include SQL query processing class
   require 'ssp.class.php';



  // Output data as json format
  echo json_encode(
   SSP::simple( $_GET, $dbDetails, $table, $primaryKey, $columns, $searchFilter )


   );

 ?>

allan

You can't do it with the SSP::simple method. You'd need to use something like:

SSP::complex( $_GET, $dbDetails, $table, $primaryKey, $columns, $searchFilter, null, [
  'column1 >= '. $_GET['minid'],
  'column1 <= '. $_GET['maxid'],
] );

This is a case where it might be easier to use Editor's PHP libraries.

If you do use the SSP script like that, then for goodness sake, make sure you validate minid and maxid as numbers. Otherwise you leave yourself open to SQL injection attacks (another reason the Editor PHP libraries would be preferable here).

Allan