Answers

• colin Posts: 15,240Questions: 1Answers: 2,599

  March 2019

  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 Posts: 127Questions: 22Answers: 0

  March 2019

  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 Posts: 127Questions: 22Answers: 0

  March 2019

  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 Posts: 127Questions: 22Answers: 0

  March 2019

  I think i am very close to a final solution.

  I will post it as soon as i make it work!
• colin Posts: 15,240Questions: 1Answers: 2,599

  March 2019

  Good to hear. Post a link or create a test case if you want any suggestions or help with any of it.
• mihalisp Posts: 127Questions: 22Answers: 0

  March 2019

  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 Posts: 63,498Questions: 1Answers: 10,470 Site admin

  March 2019

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

  Allan