Replies

• allan Posts: 63,538Questions: 1Answers: 10,476 Site admin

  March 2009

  Hi anuelicon,
  
  Yes indeed this can be done. The server-side processing isn't fully document yet since it is in a beta state, but basically DataTables expects the following data to be returned:
  
  iTotalRecords - Total records, after filtering (not just the records on this page, all of them)
  iTotalDisplayRecords - Total records, before filtering
  aaData - The data in a 2D array
  
  See this post for a few more details: http://datatables.net/forums/comments.php?DiscussionID=53&page=1#Item_1
  
  You can also see an example of how this works, including the server-side processing script here: http://datatables.net/1.5-beta/examples/data_sources/server_side.html
  
  Hope this helps,
  Allan
• korsakov Posts: 15Questions: 0Answers: 0

  March 2009

  Hi Allan, I have 2 questions for you:
  1st question:
  I am interested in DataTables server-side processing. How can I change
  "sAjaxSource": "../examples_support/server_processing.php" on the fly since my PHP receives changing values in order to put out data?
  I haven't checked out a long table but could dataTables display data while it keeps receiving data from the server?
  
  2nd question:
  I would like to read a table directly with the javascript code as to be seen in one of the examples. How do I mark up items with links. Can HTML be included to the tabel items? Any suggestion?
  
  Thank you!!
• allan Posts: 63,538Questions: 1Answers: 10,476 Site admin

  March 2009

  Hi korsakov,
  
  1. You can change the address from which DataTables gets it's data from by changing sAjaxSource in the table's settings object. For example:
  
  [code]
  var oTable;
  $(document).ready( function() {
  oTable = $('#example').dataTable( {
  "bProcessing": true,
  "bServerSide": true,
  "sAjaxSource": "/json1.php"
  } );
  
  oTable.fnSettings().sAjaxSource = "/json2.php";
  } )
  [/code]
  
  2. Yes indeed you can include html in the cells. This is the best way to put a link in if you are just looking for a plain simple link :-) - see this example: http://datatables.net/examples/example_html_sort.html
  
  Allan
• korsakov Posts: 15Questions: 0Answers: 0

  March 2009

  Thank you, Allan! Your support is really great. With regards to question 1: Do I need a variable for the "sAjaxSource" field since the PHP-URL's are constantly changing?
• allan Posts: 63,538Questions: 1Answers: 10,476 Site admin

  March 2009

  Hi korsakov,
  
  I'm not quite sure what you mean. You can use a variable if you want, I've just used an absolute string in my example. Perhaps the easiest for you would be to use a function like this:
  
  [code]
  function fnChangeSource( sSource )
  {
  oTable.fnSettings().sAjaxSource = sSource;
  }
  
  /* Example call */
  var test = "json3.txt";
  fnChangeSource( sSource );
  [/code]
  
  Then on the next table draw the table will update (note it depends on a global variable at the moment...). If you need to force a redraw, then just call oTable.fnDraw().
  
  Regards,
  Allan
• korsakov Posts: 15Questions: 0Answers: 0

  March 2009

  Thank you Allen!
  Imagine this to be the link in my HTML:
  PHP-Link
  ---------------------------------------------------------
  And this is the javascript in the header:
  
  var aTable = $('#table-test').dataTable( {
  "aoColumns": [
  { "sType": "html" },{ "sType": "html" },{ "sType": "html" },null,null
  ],
                 "bProcessing": true,
                 "bServerSide": true,
                 "sAjaxSource": "test"
         } );
  
  function fnChangeSource( sSource )
  {
         aTable.fnSettings().sAjaxSource = sSource;
  }
  
  var test = $("#table-test").click(function() {
  fnChangeSource( sSource );
  });
  --------------------------------------------------------------------
  I am sorry to take your time but at this time the table shows up without dataTables. Do you see a mistake in the javascript? It woud be great if i could make this work! Thank you!!
• korsakov Posts: 15Questions: 0Answers: 0

  March 2009

  Correction of former code:
  var aTable = $('#table-test': --!> wrong! must be the id of table of course! <-- ).dataTable( {
  
  Problem remains unfortunately..
• allan Posts: 63,538Questions: 1Answers: 10,476 Site admin

  March 2009

  Hi korsakov,
  
  Your initialisation looks good (with the exception of the id which you pointed out) - are you wrapping the $().dataTable() call in $(document).ready( function () { ... } ); ? If not this is problem the issue. If you are... could you provide a link to the page with the problem?
  
  Thanks,
  Allan
• korsakov Posts: 15Questions: 0Answers: 0

  March 2009

  Hello Allan, Thanks for your patience! I used $(document).ready( function () although I didn't include it in my recent message. Could you please email me directly because of secrecy issues? Thank you!
• korsakov Posts: 15Questions: 0Answers: 0

  March 2009

  By the way the rest of my javascript breaks with: "bProcessing"
  
  Error message:
  
  invalid property id
  [Break on this error] "bProcessing": true,\n
  
  Could this be an indicator for a problem?
• allan Posts: 63,538Questions: 1Answers: 10,476 Site admin

  March 2009

  Hi korsakov,
  
  Yes a Javascript break would probably not be good here. Although I can't see anything wrong with your initialisation, so I don't see why Javascript would be complaining about that. You can contact me directly here: http://www.datatables.net/contact - it would be great if you could indeed send me an example link (even if it's a cut down version).
  
  Allan
• hertensteinr Posts: 4Questions: 0Answers: 0

  February 2010

  hi All,
  
  I am having some problems with pagination in my Ajax code. I can get the search and sort part to work, but for some reason, the pagination is not working. One thing that I noticed is that the start position is not updating.
  
  here is the JavaScript code
  [code]
  $('#eventSearchResultsTable').dataTable({
  "bProcessing": true,
  "bServerSide": true,
  "sAjaxSource": "/siteAdmin/calendar/eventAJAXResults.php"
  });
  
  [/code]
  
  Here is the PHP Code
  [code]
  <?
  
  include($_SERVER['DOCUMENT_ROOT'] . "/customApps/includes/authentication/loginClass.php");
  
  $db = new DB_Sql();
  
  // Connect all of the GET variables to their values
  foreach($_GET as $k => $v) {
  $$k = $v;
  } // end of foreach($_GET as $k => $v) {
  
  /***********************************************
  * This function translates the column number from the table to
  * the field name of the dateabase
  **********************************************/
  function fnColumnToField( $i ) {
  switch ($i) {
  case 0:
  return "evntTitle";
  break;
  case 1:
  return "evntStartDate";
  break;
  case 2:
  return "evntStartTime";
  break;
  case 3:
  return "catMajorName";
  break;
  default:
  return "evntTitle";
  break;
  }
  } // end of function fnColumnToField( $i )
  
  
  $query = "SELECT
  e.evntID, e.evntTitle, DATE_FORMAT(e.evntStartDate, '%m-%d-%Y'), e.evntStartTime, c.catMajorName, c.catMinorName
  FROM
  cal_event AS e
  INNER JOIN cal_eventCategory AS ec ON e.evntID = ec.evcaEventID
  INNER JOIN cal_category AS c ON c.catID = ec.evcaCategoryID
  WHERE
  e.evntTitle LIKE '%$sSearch%'
  OR (e.evntStartDate LIKE '%$sSearch%')
  OR (e.evntStartDate LIKE '%$sSearch%')
  OR (c.catMajorName LIKE '%$sSearch%')
  OR (c.catMinorName LIKE '%$sSearch%')
  ";
  
  $db->query($query);
  $totalRows = $db->num_rows();
  
  if ($iSortCol_0 != "") {
  for ( $i=0 ; $i < $iSortingCols; $i++ ) {
  $query .= " ORDER BY " . fnColumnToField($_GET['iSortCol_'.$i]) . " " .$_GET['sSortDir_'.$i] .", ";
  }
  
  // knock off the extra two characters at the end of the query
  $query = substr($query, 0, strlen($query) - 2);
  }
  
  // Let's make sure we don't have bad data coming in. Let's protect the SQL
  if ($iDisplayStart == "") {
  $iDisplayStart = 0;
  }
  
  // Once again, protecting the SQL, as well as making sure we don't go over the limit
  if ($iDisplayLength == "") {
  $iDisplayLength = $totalRows - $iDisplayStart;
  }
  
  $query .= " LIMIT $iDisplayStart, $iDisplayLength ";
  
  
  $db->query($query);
  $numRows = $db->num_rows();
  
  $sOutput = '{';
  $sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
  $sOutput .= '"iTotalRecords": '.$totalRows.', ';
  $sOutput .= '"iTotalDisplayRecords": '.$numRows.', ';
  $sOutput .= '"aaData": [ ';
  
  for ($i = 0; $i < $numRows; $i++) {
  $db->next_record();
  $evntID = $db->Record[0];
  $evntTitle = $db->Record[1];
  $evntStartDate = $db->Record[2];
  $evntStartTime = $db->Record[3];
  $catMajorName = $db->Record[4];
  $catMinorName = $db->Record[5];
  
  $sOutput .= "[";
  // Column 1 -- Title/Name
  $sOutput .= '"'.addslashes($evntTitle).'",';
  // Column 2 -- Event Date
  $sOutput .= '"'.addslashes($evntStartDate).'",';
  // Column 3 -- Event Time
  $sOutput .= '"'.addslashes($evntStartTime).'",';
  // Column 4 -- Category
  $sOutput .= '"'.addslashes($catMajorName).' '.addslashes($catMinorName).'",';
  // Column 5 -- View
  $sOutput .= '"",';
  // Column 6 -- Edit
  $sOutput .= '"",';
  // Column 7 -- Delete
  $sOutput .= '""';
  $sOutput .= "],";
  }
  
  $sOutput = substr_replace( $sOutput, "", -1 );
  $sOutput .= '] }';
  
  echo $sOutput;
  
  ?>
  [/code]
  
  Any help with this would be appreciated.
  
  Thanks
• allan Posts: 63,538Questions: 1Answers: 10,476 Site admin

  February 2010

  The problem is that you only have one query - so the iTotalRecords and iTotalDisplayRecords are likely to be incorrect. I'd suggest using something like the script shown here: http://datatables.net/examples/data_sources/server_side.html . It's got three queries I know, but it's fairly optimised :-)
  
  Allan
• hertensteinr Posts: 4Questions: 0Answers: 0

  March 2010

  Actually, I have two queries that are run -- one unfiltered (which returns 13), and then the filtered [Line 51, 52], which returns 10 [Line 76, 77]. Here is the screenshot of what I am seeing
  
  http://screencast.com/t/MDg4YmViMjk
  
  I still need to stylize the table, but that's a minor thing at this time :)
  
  Thanks for the help
• allan Posts: 63,538Questions: 1Answers: 10,476 Site admin

  March 2010

  Sorry - I missed that. However, still a long the same lines, you have a limit on the query for the data, but no way to get the full data set count (I think...).
  
  $totalRows should be the total number of rows in the dataset without filtering and with out paging
  $numRows should be the total number of rows in the dataset with filtering and with out paging
  
  There is no need to tell DataTables that there are 10 records in the array - it can see that. So those two numbers tell it what it can't see. Does that help?
  
  Allan
• hertensteinr Posts: 4Questions: 0Answers: 0

  March 2010

  ok... I see what you are saying now. I have made the changes so that I run the query 3 times
  
  First time --> Total Records
  Second Time --> Filtered Records
  Third Time, --> Pagination
  
  That seems to work (with a couple of quirky things, but that's something I can work out).
  
  Thanks for the help, it's much appreciated.