Replies

• allan Posts: 63,350Questions: 1Answers: 10,443 Site admin

  December 2016

  If you are using a legacy version of DataTables (which it looks like you are from the old function names), you could just call fnDraw inside your click event handler which will redraw the table. If you have server-side processing enabled, that means that an Ajax request will be made for you.

  Allan
• plica2006 Posts: 7Questions: 0Answers: 0

  January 2017

  Hi Allan,

  Thanks for responding and also for making Datatables which is so useful and really brilliant.

  Basically I could have phrased my original post.. "What is Ajax and how does it work?" I did alot of googling and learned a bit about it which has given me a better understanding of how Datatables works. I didn't realise the webpage wasn't reloading just the DIV that contains the Datatable. That's really cool.

  Basically I understand that calling myDatatable.fnDraw() makes an Ajax request to get more data from Server Side and then re-sort and re-draw the whole Datatable. But all I wanted to do was get an extra bit of info from the database based on a user clicking an individual row on the table. I then wanted to display that information on a popup. I thought fnDraw() would be an inefficient use of the database server that could slow things down and make for hacky code.

  Here is the (hacky!) code I put together in case anyone else needs it. When you click on a column in the table jquery gets some properties of its row and uses Ajax to make a quick request to the database to get some additional info to be displayed on a popup.

  In my main php file that contains the Datatable:

  $(document).ready(function() {
  
      var oTable = $("#myDataTable").dataTable( {
       // Initialise the datatable
  });
  
  $(document).on("click","table.display td.item_name", function() { 
      /*
       * Get the Shelf and Channel number of the item name that was clicked on
       */
          var aRow = $(this).closest("tr"),
          thisShelf = aRow.find("td:nth-child(1)"),
          thisChannel = aRow.find("td:nth-child(2)");
              //alert("Shelf is: " + thisShelf.text() + " Channel is: " + thisChannel.text());
      /*
       * Use AJAX to make an asynchronous call to the DB to get the extra data for the item that was clicked on.
       */            
          $.ajax({                                      
              url: "Includes/Ajax.php",       
              type: "GET",
              data: { "queryType": "select", "shelf" : thisShelf.text(), "channel" : thisChannel.text()},
              dataType: "json",       //data format      
              success: function(data) //on recieve of reply
              {           
              alert("Success, Data Length is: " + data.length);
              var item = data[0];
              alert("OID: " + item.oid + " Shelf: " + item.shelf2 + " Channel: " + item.bin + " Name: " + item.name + " Quantity: " + item.stock);
              },
              error: function (request, status, error) {
                  alert(request.responseText);
              }
          });
      $('#my_popup').popup('show');
  }); 
  
  }); // end of $(document).ready
  

  Now in Ajax.php....

  ```
  <?php

  include "functions.php";

  // Code goes here to connect to the database

  // Get the value passed from Ajax
      $squeryType = trim($_GET['queryType']);
      //error_log("Query Type is: " . $squeryType);
  

  /==============================================================================
  * Execute the Ajax Select Query
  *============================================================================/
  if( $squeryType === 'select'){
  // Get the Shelf and Channel number of the Item that was clicked on
  $sShelf = trim($_GET['shelf']);
  $sChannel = trim($_GET['channel']);
  // Execute the Ajax Select Query
  // function ajax_select() just assembles the Query String and is defined in functions.php
  $sAjaxSelectQuery = ajax_select($sShelf,$sChannel);
  //error_log("Ajax Select Query is: " . $sAjaxSelectQuery);

      if (pg_send_query($link, $sAjaxSelectQuery)) {
        $res=pg_get_result($link);
        if ($res) {
          $state = pg_result_error_field($res, PGSQL_DIAG_SQLSTATE);
          if ($state==0) {
            // success
              //$numrows = pg_numrows($res);
          }
          else {
              error_log("Error : Some kinda error happened when connecting to the database performing the Ajax Select Query!\n");
          }
        }  
      }
  
  // Names in $pgColumns are used to retrieve the column values from the $pgRow array below.
  // Column names in $pgColumns must match the column names returned by the SQL Query.
      $pgColumns = array('oid','shelf2','bin','name','stock'');
      $pgOutput = array();
  
      while ( $aRow = pg_fetch_array( $res ) ) {
      $pgRow = array();
          for ( $i=0 ; $i<count($pgColumns) ; $i++ ) {
              if ( $pgColumns[$i] != ' ' ) {
                  $v = $aRow[ $pgColumns[$i] ];
                  $v = mb_check_encoding($v, 'UTF-8') ? $v : utf8_encode($v);
                  $pgRow[]=trim($v);
              }
          }
          If (!empty($pgRow)) {
              $pgOutput[] = array(
                  'oid'   => $pgRow[0],
                  'shelf2'  => $pgRow[1],
                  'bin'  => $pgRow[2],
                  'name'  => $pgRow[3],
                  'stock'  => $pgRow[4]
                  );
              }
      }
      //header('Content-Type: application/json');
      echo json_encode($pgOutput);            
  }     
  

  pg_close($link);

  <?php > ``` I used some of your ServerSide processing code to create an array of JSON formatted data to return to the AJAX calling function --> success: function(data){} in my main php page. ?>

  So there it is. I guess it ain't pretty, I'm just a hobbyist coder. But for my little use case it does what I need and I enjoyed figuring out at least this much.

  Thank you and happy coding