How using range datepicker jquery UI to filter datatable boostrap4 ?

aubin

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

Rob_

I could not get it work with Editor:: library b/c I needed to do BETWEEN SQL queries, so went with the SSP:: library. Note: the json structure between the two libraries are different.

There is a weird bug that's causing the pagination to get lost after the second date pick.

(function($){
    $(document).ready(function(){

        $.fn.dataTable.ext.errMode = 'throw';

        $("#dpicker").datepicker({
          showOn: "button",
          buttonImage: "./images/calendar.gif",
          buttonImageOnly: true,
          buttonText: "Select date",
          maxDate: "0",
          autoclose: true,
          onClose: function(selectedDate, inst) {   
            var table1 = $('#your_data').DataTable({
                destroy: true,
                                dom: 'Bfrtip',
                ajax: {
                      url: "php/table.your_data.php",
                      data: { varDate: new String(selectedDate) },
                      dataType: 'json'
                      }
            });
          }
        });
    });
}(jQuery));

<?php
 
$varDate = $_GET['varDate'];

$dtStart = date_create($varDate);
date_time_set($dtStart,7,0);

$dtEnd = date_create($varDate);
date_add($dtEnd, date_interval_create_from_date_string("1 days"));
date_time_set($dtEnd,7,0);

$where = 'time_of_event BETWEEN \'' . date_format($dtStart,'Y-m-d H:i:s') . '\''  . ' AND ' . '\'' . date_format($dtEnd,'Y-m-d H:i:s') . '\'';

// DB table to use
$table = 'your_data';
 
// Table's primary key
$primaryKey = 'your_data_id';
 
// 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' => 'time_of_event', 'dt' => 0),
    array( 'db' => 'sku', 'dt' => 1 ),
    array( 'db' => 'day_count_index', 'dt' => 2 ),
    array( 'db' => 'trigger_index', 'dt' => 3 ),
    array( 'db' => 'width_mm', 'dt' => 4 ),
    array( 'db' => 'length_mm', 'dt' => 5 ),
    array( 'db' => 'area_mm2', 'dt' => 6 ),
    array( 'db' => 'nc_class', 'dt' => 7 )
);
 
// Enable error reporting for debugging (remove for production)
error_reporting(E_ALL);
ini_set('display_errors', '1');

// SQL server connection information
$sql_details = array(
    "type" => "Mysql",
    "user" => "xxxx",
    "pass" => "xxxx",
    "host" => "localhost",
    "port" => "",
    "db"   => "bfo1655",
    "dsn"  => "charset=utf8"
);
 
 
require( 'ssp.class.php' );
 
echo json_encode(
    SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, $where )
);

Rob_

There is a weird bug that's causing the pagination to get lost after the second date pick.

This occurs if responsive: true

allan

While we don’t have a BETWEEN method for the Editor libraries, you could readily use a greater than and less than condition - e.g.:

->where( function ($q) {
  $q->where(‘column’, $_POST[‘min’], ‘>’);
  $q->where(‘column’, $_POST[‘max’], ‘<’);
} )

I’d also suggest not building a new DataTable on every date selection - just call draw() on an existing table to have it redraw (and refilter). ajax.data can be given as a function to recalculate the values to send to the server on each draw (when using server-side processing).

Allan

Rob_

Thanks Alan. That fixed the responsive related error.