Server-side Processing with Hidden Rows

imdabeef

I can't seem to get the hidden rows example working with the server-side processing. I can get the hidden rows example to work correctly, but when I add in the server-side processing, I get the following error on page load: "DataTables warning (table id = 'example'): Requested unknown parameter '4' from the data source for row 0"

Here is my Javascript code:

allan

[code]


function fnFormatDetails ( oTable, nTr )
{
var aData = oTable.fnGetData( nTr );
var sOut = '';
sOut += ''+aData[1]+'';
sOut += '';

return sOut;
}

$(document).ready(function()
{
/*
* Insert a 'details' column to the table
*/
var nCloneTh = document.createElement( 'th' );
var nCloneTd = document.createElement( 'td' );
nCloneTd.innerHTML = '';
nCloneTd.className = "center";

$('#example thead tr').each( function () {
this.insertBefore( nCloneTh, this.childNodes[0] );
} );

$('#example tbody tr').each( function () {
this.insertBefore( nCloneTd.cloneNode( true ), this.childNodes[0] );
} );

/*
* Initialse DataTables, with no sorting on the 'details' column
*/
var oTable = $('#example').dataTable( {
"aoColumnDefs": [
{ "bSortable": false, "aTargets": [ 0 ] },
{ "bVisible": false, "aTargets": [ 1 ] }
],
"aaSorting": [[1, 'asc']],
"sScrollY": "500px",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_processing.php"
});

/* Add event listener for opening and closing details
* Note that the indicator for showing which row is open is not controlled by DataTables,
* rather it is done here
*/
$('#example tbody td img').live('click', function () {
var nTr = this.parentNode.parentNode;
if ( this.src.match('details_close') )
{
/* This row is already open - close it */
this.src = "gfx/details_open.png";
oTable.fnClose( nTr );
}
else
{
/* Open this row */
this.src = "gfx/details_close.png";
oTable.fnOpen( nTr, fnFormatDetails(oTable, nTr), 'details' );
}
} );


});



HERE IS MY TABLE:





Notes
Phone Number
Hunt
Type




Loading Data...




HERE IS MY SERVER PROCESSING:

<?php
/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* Easy set variables
*/

/* Array of database columns which should be read and sent back to DataTables. Use a space where
* you want to insert a non-database field (for example a counter or static image)
*/
$aColumns = array( ' ', 'notes','phone_number', 'hunt', 'type' );

/* Indexed column (used for fast and accurate table cardinality) */
$sIndexColumn = "XXXXXXXXXX";

/* DB table to use */
$sTable = "XXXXXXXXX";

/* Database connection information */
$gaSql['user'] = "XXXXXXXXX";
$gaSql['password'] = "XXXXXXXXX";
$gaSql['db'] = "XXXXXXXXX";
$gaSql['server'] = "XXXXXXX";


/* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* If you just want to use the basic configuration for DataTables with PHP server-side, there is
* no need to edit below this line
*/

/*
* MySQL connection
*/
$gaSql['link'] = mysql_pconnect( $gaSql['server'], $gaSql['user'], $gaSql['password'] ) or
die( 'Could not open connection to server' );

mysql_select_db( $gaSql['db'], $gaSql['link'] ) or
die( 'Could not select database '. $gaSql['db'] );


/*
* Paging
*/
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )
{
$sLimit = "LIMIT ".mysql_real_escape_string( $_GET['iDisplayStart'] ).", ".
mysql_real_escape_string( $_GET['iDisplayLength'] );
}


/*
* Ordering
*/
$sOrder = "";
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i $iFilteredTotal,
"aaData" => array()
);

while ( $aRow = mysql_fetch_array( $rResult ) )
{
$row = array();
for ( $i=0 ; $i
[/code]

allan

Given this:

> $aColumns = array( ' ', 'notes','phone_number', 'hunt', 'type' );

I would suspect that you are actually getting either invalid JSON or empty JSON in the return since it will likely be building invalid SQL. You almost certainly don't want the first element you've got there.

What I would suggest instead of that is to modify the output array. Where you have $row = array(); for example you could add $row[] = ""; to get an empty string there.

This example should help: http://datatables.net/release-datatables/examples/server_side/row_details.html

Allan

imdabeef

Thank you Allan. Sorry did not see that link in the examples. This is most helpful and has helped me resolve my issue.

Thank you.

imdabeef

Allan, I noticed that in the example: http://datatables.net/release-datatables/examples/server_side/row_details.html

The sorting is actually being done by the column on the right or the column header that is actually clicked on.

I changed:

$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] ) ]."
".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";

To:

$sOrder .= $aColumns[ intval( $_GET['iSortCol_'.$i] - 1 ) ]."
".mysql_real_escape_string( $_GET['sSortDir_'.$i] ) .", ";

Do you think this was the best option?

allan

Good spotting - yes that is a bug in my script. I'd say your change is the correct one to make since you control the columns in the table and on the server-side. I'll just commit that fix myself... :-)

Allan