Answers

• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  April 2015

  Use ajax.data as a function rather than a static object. That way you can read the values from the DOM and insert them into the request object on-the-fly.

  Allan
• webpointz Posts: 126Questions: 30Answers: 4

  April 2015

  I tried that too. The variables seem to be passed, BUT, in the ssp.class.php file, how do I read them in and use them in a where clause like the following:

  WHERE customerid= 12 AND (date(insertdate) >= '2015-03-30' AND date(insertdate) <= '2015-04-06')
  

  I try $customerid = $_POST['formCustomer']; etc to no avail.
• webpointz Posts: 126Questions: 30Answers: 4

  April 2015

  So, in my server_processing.php I AM getting the variables as $_POST type variables, but the $where clause passed through the

  echo json_encode(
      SSP::simple( $_GET, $sql_details, $table, $primaryKey, $columns, $where)
  );
  

  isn't working.
• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  April 2015 Answer ✓

  The SSP class is designed to be super simple. It will require modifications if you want to perform some more complex conditions beyond just the simple global and column options of DataTables.

  Have a look at the complex static method however, it also will require modification to match your needs.

  Allan
• webpointz Posts: 126Questions: 30Answers: 4

  April 2015

  Hi Allan, yes, I'm using the "complex" example above and it is now filtering.

  One problem though is that when the results are returned to the table instead of the default "showing 10" records, it shows ALL 16 records with 2 pages in pagination and says "Showing 1 to 10 of 16 entries" when in fact it is showing ALL records.
• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  April 2015

  Are you using the whereResult or whereAll option of the complex method. Sounds like you want whereAll.

  Allan
• webpointz Posts: 126Questions: 30Answers: 4

  April 2015

  I try both with the same result
• webpointz Posts: 126Questions: 30Answers: 4

  April 2015

  It's as though anything above 10 records breaks pagination.
• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  April 2015

  Can you link to the page and show me the code please?
• webpointz Posts: 126Questions: 30Answers: 4

  April 2015

  It's behind a login but I can send you credentials via email with steps to the page in question and send you the code.
• webpointz Posts: 126Questions: 30Answers: 4

  April 2015

  Seeing as this particular datatable is for a report, I turned off pagination, however, there seems to be a bug with the "complex" PHP file and pagination.
• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  April 2015

  I've just tried this with my basic server-side processing example:

  echo json_encode(
      SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, "first_name like '%a%'" )
  );
  

  And it seems to work as expected (including specifically the paging).

  Allan
• webpointz Posts: 126Questions: 30Answers: 4

  April 2015 edited April 2015

  Hi Allan

  I'm using the following:

  $dateToday = date("Y-m-d");
  
  $customerid = $_POST['formCustomer'];
  
  if ( isset($_POST['formStartDate']) && $_POST['formStartDate'] != '' ) {
      $startdate = $_POST['formStartDate'];
  } else {
      $startdate = '2010-01-01';
  }   
  
  if ( isset($_POST['formEndDate']) && $_POST['formEndDate'] != '' ) {
      $enddate = $_POST['formEndDate'];
  } else {
      $enddate = '.$dateToday.';
  }   
  
  
  
  /*
   * DataTables example server-side processing script.
   *
   * Please note that this script is intentionally extremely simply to show how
   * server-side processing can be implemented, and probably shouldn't be used as
   * the basis for a large complex system. It is suitable for simple use cases as
   * for learning.
   *
   * See http://datatables.net/usage/server-side for full details on the server-
   * side processing requirements of DataTables.
   *
   * @license MIT - http://datatables.net/license_mit
   */
  
  /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
   * Easy set variables
   */
  
  // DB table to use
  $table = 'activityreport';
  
  // 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, while the `dt`
  // parameter represents the DataTables column identifier. In this case simple
  // indexes
  $columns = array(
      array( 'db' => 'productid', 'dt' => 0 ),
      array( 'db' => 'username',   'dt' => 1 ),
      array( 'db' => 'ordernumber',  'dt' => 2 ),
      array( 'db' => 'orderdate',             
          'dt' => 3,
          'formatter' => function( $d, $row ) {
              return date( 'Y-m-d', strtotime($d));
          }
      ),
      array( 'db' => 'qty_sum',  'dt' => 4 )  
  );
  
  
  require( '../edi-includes/sql-connect.php' );
  
  
  /* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
   * If you just want to use the basic configuration for DataTables with PHP
   * server-side, there is no need to edit below this line.
   */
  
  require( 'ssp.class.php' );
  
  $whereAll = " customerid =".$customerid." AND date( orderdate ) >= '".$startdate."' AND date( orderdate ) <= '".$enddate."'";
  
  echo json_encode(
      SSP::complex( $_GET, $sql_details, $table, $primaryKey, $columns, null, $whereAll)
  );
  

  Running against "ssp.class.php" in your static link to GitHub.
• webpointz Posts: 126Questions: 30Answers: 4

  April 2015

  Allan: I found the issue but not sure how to fix it. The issue is with the <script> code on the calling page with AJAX. The issue is with AJAX and DATA and how it's constructed.
  It brings back data and displays it BUT all records show but pagination shows pages etc and also search and sort at headers doesn't work either.

  What am I doing wrong here?

  Here is my code.

      <script type="text/javascript" language="javascript" class="init">
  
          $.extend( $.fn.dataTable.defaults, {
              responsive: true
          } );        
          
          $(document).ready(function() {
              $('#example').dataTable( {
                  
                  "language": { "infoFiltered": ""},          
                  "order": [[ 1, "desc" ]],
                  "processing": true,
                  "serverSide": true,
          
                  "ajax": {
                          "type": 'POST',
                          "url": 'scripts/server_processing_activity.php',
                  "data": {                        
                      formCustomer: '<?php echo $_POST["sel_customer"] ?>',  
                      formStartDate: '<?php echo $_POST["dp3"] ?>',
                      formEndDate: '<?php echo $_POST["dp4"] ?>',
                      }               
                  }               
              } );
          } );
  
      </script>   
  
• webpointz Posts: 126Questions: 30Answers: 4

  April 2015

  Just found it. As soon as I removed

  "type": 'POST',
  

  Everything works!?
• allan Posts: 61,972Questions: 1Answers: 10,160 Site admin

  April 2015

  Your server_processing_activity.php script is expecting GET parameters then (line 73 in the PHP script above uses $_GET confirming this).

  Allan
• webpointz Posts: 126Questions: 30Answers: 4

  April 2015

  Thanks Allan, I removed the "type": 'POST' and changed the parameters in the server_processing_activity.php to be $_GET and everything is running fine now.

  Many thanks...what a great product!