Answers

• Caldaga Posts: 24Questions: 6Answers: 0

  May 2017

  Also, if I knew for sure that someone would be able to help me get this project working as intended in a fairly timely fashion, I would be willing to purchase some support credits if that helps.
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  May 2017

  I'm not familiar with PHP (I use Python) but thought I would take a look. It looks like the problem is the cursor type you are using which I believe is the default of forward.

  Your code:

  /* Data set length after filtering */
      $sQueryCnt = "SELECT * FROM $sTable $sWhere";
      $rResultCnt = sqlsrv_query($conn, $sQueryCnt );
      $iFilteredTotal = sqlsrv_num_rows( $rResultCnt );
   
  /* Total data set length */
      $sQuery = "SELECT COUNT(id) FROM $sTable";
      $rResultTotal = sqlsrv_query($conn, $sQuery );
      $aResultTotal = sqlsrv_fetch_array($rResultTotal, SQLSRV_FETCH_NUMERIC);
      $iTotal = $aResultTotal[0];
  

  According to the sqlsrv_num_rows docs you will get the false response if there is an error or if the wrong cursor type ("forward", "dynamic") is used. You need to use either "static" or "keyset".

  You can set this in the sqlsrv_query query using options.

  Maybe you need to do something like this:
  sqlsrv_query($conn, $sql, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET))

  Kevin
• Caldaga Posts: 24Questions: 6Answers: 0

  May 2017

  I changed my code to this:

  /* Data set length after filtering */
  $sQueryCnt = "SELECT * FROM $sTable $sWhere";
  $rResultCnt = sqlsrv_query($conn, $sQueryCnt, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
  $iFilteredTotal = sqlsrv_num_rows( $rResultCnt );

  /* Total data set length */
  $sQuery = "SELECT COUNT(id) FROM $sTable";
  $rResultTotal = sqlsrv_query($conn, $sQuery, array(), array("Scrollable" => SQLSRV_CURSOR_KEYSET));
  $aResultTotal = sqlsrv_fetch_array($rResultTotal, SQLSRV_FETCH_NUMERIC);
  $iTotal = $aResultTotal[0];

  And recieve same errors unfortunately.
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  May 2017 edited May 2017

  Did the change fix this the "recordsFiltered":false by returning the correct value?

  You are getting "null" for the recordsTotal field. This would be a different issue. Your SQL query is "SELECT COUNT(id) FROM $sTable". Does your table have an id column? If not then you will need to change to a primary key column.

  Kevin
• kthorngren Posts: 21,343Questions: 26Answers: 4,954

  May 2017 Answer ✓

  Also if you look at the tech note link:
  https://datatables.net/manual/tech-notes/4

  It mentions that if the unknown parameter is an integer then Datatables is expecting an array. You are retuning a data object which means that you need to define the column info using columns.data.

  Kevin
• Caldaga Posts: 24Questions: 6Answers: 0

  May 2017

  My data is in the table! I appreciate your help so much. Now I just need to figure out how to do this dynamically as the intent is to allow the users to select which columns to pull out of the table.

  In case anyone else has this problem this is my new Javascript that resolved the problem:

      <script type="text/javascript">
          $(document).ready(function() {
              $('#myTable').DataTable( {
                  dom: 'Bfrti',
                  buttons: [ 'colvis' ],
                  scrollY: 700,
                  deferRender: true,
                  scroller: true,
                  processing: true,
                  serverSide: true,
                  ajax: {
                      url: '../php/queryresults.php',
                      type: "POST"
                  },
                  columns: [
                      { "data": "Action" },
                      { "data": "TimeOccurred" },
                      { "data": "UserName" },
                      { "data": "IPv4From" },
                      { "data": "ShareName" },
                      { "data": "FullFilePath" },
                      { "data": "NewPathName" },
                      { "data": "FromServer" }
                  ]
              } );
          } );
      </script>
  
• allan Posts: 63,535Questions: 1Answers: 10,475 Site admin

  May 2017

  The key to doing it dynamically is to built an array that you can pass into the columns option. In this case it looks like you might just need to create an array of objects with the data property set.

  Allan
• Caldaga Posts: 24Questions: 6Answers: 0

  May 2017

  Thanks for the response Allen. This is what I did ultimately for now:

      <script type="text/javascript">
          $(document).ready(function() {
              $('#myTable').DataTable( {
                  dom: 'Bfrti',
                  buttons: [ 'colvis' ],
                  scrollY: 700,
                  deferRender: true,
                  scroller: { loadingIndicator: true },
                  processing: true,
                  serverSide: true,
                  ajax: {
                      url: '../php/queryresults.php',
                      type: "POST"
                  },
                  <?php
                  $myColumns = $_POST['selectedcolumns'];
                  echo "columns: [";
                  foreach ($myColumns as $col) {
                      echo '{ "data": "'.$col.'" },';
                      unset($col);
                  }
                  echo "]";
                  ?>
              } );
          } );
      </script>
  

  I still need to figure out how to get my other variables passed from form POST to the server-side script. And then I need to do some research on XSS and SQL Injection and make sure I do whatever I can to mitigate that risk.
• Caldaga Posts: 24Questions: 6Answers: 0

  May 2017

  New problem I am having is that when the data from my SQL server goes through the json_encode it adds information onto the date column.

  {
      "draw":0,
      "recordsTotal":21877,
      "recordsFiltered":21877,
      "data":
      [
          {
          "Action":"Rename Folder",
          "TimeOccurred":{"date":"2017-04-26 10:08:00","timezone_type":3,"timezone":"America\/Tegucigalpa"},
          "UserName":"Username",
          "IPv4From":"ipaddress",
          "ShareName":"",
          "FullFilePath":"C:\\ProgramData\\USOShared\\Logs",
          "NewPathName":"C:\\ProgramData\\USOShared\\Logs\\UpdateUx.100.etl",
          "FromServer":"HOSTNAME"
          },
          {
          "Action":"Delete Confirmed for File",
          "TimeOccurred":{"date":"2017-04-26 10:08:00","timezone_type":3,"timezone":"America\/Tegucigalpa"},
          "UserName":"username",
          "IPv4From":"ipaddress",
          "ShareName":"",
          "FullFilePath":"C:\\ProgramData\\USOShared\\Logs",
          "NewPathName":"",
          "FromServer":"hostname"
          },
          {
          "Action":"Create File",
          "TimeOccurred":{"date":"2017-04-27 10:37:00","timezone_type":3,"timezone":"America\/Tegucigalpa"},
          "UserName":"",
          "IPv4From":"ipaddress",
          "ShareName":"?share path?",
          "FullFilePath":"?share path?",
          "NewPathName":"",
          "FromServer":"hostname"
          },
      ]
  }
  

  I think the best way to handle this might be to do something with it in the server side script before it is passed to json_encode, but I'm not sure that will work because that information is being added somewhere as it doesn't exist within SQL when I do a query.
• allan Posts: 63,535Questions: 1Answers: 10,475 Site admin

  June 2017

  I'm not sure what would be causing that. Are you using a DateTime object for the TimeOccurred property? If so, that's probably the issue. Convert it to be a string first.

  Allan
• Caldaga Posts: 24Questions: 6Answers: 0

  June 2017

  Yea trying to figure out how I would convert it to a string preferably on the server side. Attached is my server-side script.

  This is my Javascript:

      <script type="text/javascript">
          $(document).ready(function() {
              $('#myTable').DataTable( {
                  dom: 'Bfrti',
                  buttons: [ 'colvis' ],
                  scrollY: 700,
                  deferRender: true,
                  scroller: { loadingIndicator: true },
                  processing: true,
                  serverSide: true,
                  ajax: {
                      url: '../php/queryresults.php',
                      type: "POST",
                      data: 
                      { 
                          table: '<?php echo $_POST['table']; ?>',
                          filtercolumn: '<?php echo $_POST['filtercolumn']; ?>',
                          filtervalue: '<?php echo $_POST['filtervalue']; ?>',
                          filterexpression: '<?php echo $_POST['filterexpression']; ?>',
                          daterange: '<?php echo $_POST['datefilter']; ?>',
                          selectedcolumns: '<?php echo $_POST['selectedcolumns']; ?>'
                      },
                  },
                  <?php
                  $myColumns = $_POST['selectedcolumns'];
                  echo "columns: [";
                  foreach ($myColumns as $col) {
                      echo '{ "data": "'.$col.'" },';
                      unset($col);
                  }
                  echo "]";
                  ?>
              } );
          } );
      </script>
  

  I'm having a hard time figuring out how to convert that one column TimeOccurred to a string, could I consider just converting everything in the 'data' array to a string? If I do that does it mess up filtering/sorting on the date column?

  Also in my server side script I have this line to do the columns:

  $aColumns = $_POST['selectedcolumns'];

  And getting invalid json object error back because of it, even though I'm pretty sure that I'm assigning it correctly unless ajax isn't passing it as an array or something.
• Caldaga Posts: 24Questions: 6Answers: 0

  June 2017

  Since my main problem here was resolved I think I'll focus on one issue at a time and open separate forum posts to avoid confusion and get more attention on the post. Thanks for all your help guys.