Filtering sql query by bootstrap-datepicker values

mihalisp

I have 2 bootstrap datepickers(#start,#end) and a Datatable which returns 150.000 rows from MS SQL Server via PHP.I want by default the start date to be 7 days earlier nad the end date to be Today.

I need to find a way to filter the Sql Query (in a PHP file) with WHERE condition between #start and #end datepicker values.If i use no filter the returned JSON is 60 MBs which freezes the browser and consequently i can't even filter the Datatable in the client.

I have already used $.fn.dataTable.ext.search.push to filter the Datatable on the client and it works ,but i want to filter the JSON output from the Sql Query before it returns to the browser.

How can i dynamically pass these date values via ajax.url in the Sql Query which is in the backend PHP file?Is serverside processing the only way or it can be done with the following code?

   var table = $('#example').DataTable({

 //"serverSide": true,
 "ajax" : { url: 'queries.php?q=employees_full&start='+start'&end='+end ,
              dataType: "json",
              dataSrc: '' },   
   "autoWidth": true,
   "deferRender": true ,
    "columns": [
    { "data": "username" },............

});

  var curdate = moment().subtract(7, 'day').toDate();

   $( '#end' ).datepicker( 'setDate', new Date() );
   $( '#start' ).datepicker( 'setDate', curdate );

   var start = $('#start').val();
   var end   = $('#end').val();

  $("#start").datepicker({ language: 'el', autoclose: true,              
      onchangeDate: function () { 

    var start = $('#start').val();
    table.ajax.url('queries.php?q=employees_full&start='+start'&end='+end); //do i miss a + here??
    //table.ajax.reload();
      table.draw(); }});

  $("#end").datepicker({ language: 'el', autoclose: true,
      onchangeDate: function () {  
    var end = $('#end').val();
      table.ajax.url('queries.php?q=employees_full&start='+start'&end='+end); //do i miss a + here??
     //table.ajax.reload();table.draw(); }});


    $('#start, #end').change(function () {
     //table.ajax.url('queries.php?q=employees_full&start='+start+'&end='+end);
      // table.ajax.reload();            
        table.draw();          
      }); 

1)What is the difference between table.ajax.reload() and table.draw ? Which one should i use in my case?

2)i also get an error in JSON output"Conversion failed when converting date and/or time from character string." .Probably the #start datepicker value is undefined,i don't know why??

3)Is the order of the commands right?When/how many times should i get the value of $('#start') and $('end')?What is wrong with my code?

4)The dates in the Datepicker are in Format dd/mm/yyy but in the Sql Query queries.php?q=employees_full are in Format yyyy-mm-dd.I use prepared statements and have used the following:

     WHERE sql_date  between  (CONVERT(date,SUBSTRING(CONVERT(varchar, ?, 100), 4, 3) +SUBSTRING(CONVERT(varchar, ?, 100), 1, 3)+SUBSTRING(CONVERT(varchar, ?, 100), 7, 4)))

and

      $stmt = sqlsrv_prepare( $conn, $sql , array(&$_GET["start"],&$_GET["start"],&$_GET["start"]));

colin

Hi @mihalisp ,

A few things there.

1. use ajax.data to send additional information to the server scripts
2. ajax.reload() is used to ask the server to resend the data, which is the same as draw() if serverSide is enabled. If all the data is on the client, and ajax isn't set, then ajax.reload() will do nothing.
3. you can use Moment.js, as it's excellent for all time/date based operations - see example for date conversions here.

For the remaining ones, it would be useful to see the code. Information on how to create a test case (if you aren't able to link to the page you are working on) is available here.

Cheers,

Colin

mihalisp

Thank you Colin.

1.If i understand what you are saying ,i can use this

   "data": function ( d ) {
    d.start_date = $('#start').val();
    d.end_date  = $('#end').val();    }

to pass the 2 date values from the datepickers to my php sql query

  queries.php?q=employees_full   
  // &start='+start+'&end='+end  this is not needed as the values will be sent with POST ??

In the sql query in php file the code should be like this:

   ....WHERE Sql_Table.sql_date  between data.start_date and  data.end_date

2.In my case i don't use Serverside processing in the Datatable,but the table.draw command works fine when the bootstrap datepicker changes dates.Why .draw works here?Can i have serverside processing enabled without using ssp.class.php

     $("#apo").datepicker({ language: 'el', autoclose: true, onchangeDate: function () {    table.draw(); }});
     $("#ews").datepicker({ language: 'el', autoclose: true, onchangeDate: function () { table.draw(); }});

     // Event listener to the two range filtering inputs to redraw on input
     $('#apo, #ews').change(function () {
          table.draw(); }); 

mihalisp

After a lot of research,i ended up to this.

I initialize the bootstrap datepickers and with the ajax.data option i pass the parameters(date objects) to the php Sql Query.

1)Is it better to pass the parameters as date objects instead of strings so that the conversion and comparison in the Sql Query is easier?

2)Should the initialisation of the Datepickers be before or after the Datatable?

3)Should i use 2 events .change and .on('changedate') for the datepickers so that both values to have been set before the initialisation of the datatable?

When i load the page , I get error : input1 is not defined

and after that when i pick different dates,i get TypeError: table is undefined.
I still can't find the right order of the commands i should use.

Any help would be appreciated!

          var curdate = moment().subtract(7, 'day').toDate();

          $("#start,#end").datepicker({ language: 'el', autoclose: true});
          $( '#end' ).datepicker( 'setDate', new Date() );
          $( '#start'  ).datepicker( 'setDate', curdate );

           $('#start, #end').datepicker({ language: 'el', autoclose: true}).change(function () {

           //var thisval = this.value; //this.val--> undefined
           var start= $( '#apo' ).datepicker( 'getUTCDate' );// returns datetime object
           var end= $( '#ews' ).datepicker( 'getUTCDate' );// returns datetime object

           var input1={ startdate: $( '#start' ).datepicker( 'getUTCDate' ), 
                               enddate: $( '#end' ).datepicker( 'getUTCDate' ) };

            //table.ajax.url("queries.php?q=employees_full"); //do i need this?
            //table.ajax.reload(); //or table.draw();
            });      

           var table = $('#example4').DataTable({
                            "ajax" : { url: "queries.php?q=employees_full" ,
                                          dataType: "json",
                                          dataSrc: '' ,
                                          data: input1 },
                                            ......});

In the PHP Sql Query queries.php?q=employees_full i prepare the statement with

          ... WHERE alldates.alldate >= (CONVERT(date, ?))  and alldates.alldate <= (CONVERT(date, ?))

and

           $stmt = sqlsrv_prepare( $conn, $sql , array(&$_POST["startdate"],&$_POST["enddate"]));

What am i missing?

mihalisp

I think i am very close to a final solution.

I will post it as soon as i make it work!

colin

Good to hear. Post a link or create a test case if you want any suggestions or help with any of it.

mihalisp

Well,'data:...' was the key to the parameters i pass to the sql query.

      var input1;

      var curdate = moment().subtract(1, 'day').toDate();
       $("#start,#end").datepicker({ language: 'el', autoclose: true});
       $( '#end' ).datepicker( 'setDate', new Date() );
       $( '#start' ).datepicker( 'setDate', curdate );

       $('#start,#end').datepicker({ language: 'el', autoclose: true}).change(function () {

        var start= $( '#start' ).val();
        var end= $( '#ews' ).val();
        input1={ startdate:start, enddate: end };
        table.ajax.reload();
        });  

       if (( input1 == null) ||( input1 == undefined)) { input1={ startdate: $('#start').val(), enddate: $('#end').val() };}

       else { input1={ startdate: $('#start').val, enddate: $('#end').val };}

       var table = $('#example').DataTable({

       "ajax" : 
       {   url: "queries.php?q=employees....",
           dataType: "json",
           dataSrc: '' ,
           type: "POST",
           //data: input1 
           "data": function ( d ) {
                     d.startdate= $('#start').val();
                     d.enddate= $('#end').val();    }

and the sql query:

  ...WHERE sql_date >= (convert (date,SUBSTRING(CONVERT(varchar,?,100), 4, 3) 
 +SUBSTRING(CONVERT(varchar, ?, 100), 1, 3)
 +SUBSTRING(CONVERT(varchar, ?, 100), 7, 4)))  and 
   sql_date <= (convert (date,SUBSTRING(CONVERT(varchar, ?, 100), 4, 3) 
 +SUBSTRING(CONVERT(varchar, ?, 100), 1, 3)
 +SUBSTRING(CONVERT(varchar, ?, 100), 7, 4))) 

  $stmt = sqlsrv_prepare( $conn, $sql , array(&$_POST["startdate"],
 &$_POST["startdate"],&$_POST["startdate"],
 &$_POST["enddate"],&$_POST["enddate"],
 &$_POST["enddate"]));

Any comments?

allan

input1 looks redundant, but other than that, it looks fine .

Allan