Pagination not working

Miles

Hi,

Firstly, love datatables to death, especially with the jeditable built in as well makes my life so much easier! Pagination is working a tad weird however. In full numbers mode, I can click the individual numbers to change page, however for some reason when going onto the second page it offers a third page, yet I only have 12 results.

In normal mode, it won't let me change page at all. It's coming up disabled.

This is a server side table. Sorry I can't provide a live example as this is for an intranet site.

My javascript:
[code]var oTable;

$(document).ready(function() {

/* Init DataTables */
oTable = $('#dt').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "./includes/grouptable.php",
"aaSorting": [[ 1, "asc" ]],
"bPaginate":true ,
"aoColumns": [
{ "bSearchable": false,
"bVisible": false },
null,
null,
null,
{ "bSearchable": false,
"bVisible": false }
],
"fnDrawCallback": function() {
$('#dt tbody td').editable( './includes/editultable.php', {
"callback": function( sValue, y ) {
var aPos = oTable.fnGetPosition( this );
oTable.fnUpdate( sValue, aPos[0], aPos[1] );
},
"onsubmit": function ( settings, self ) {
var aPos = oTable.fnGetPosition( self );
var aData = oTable.fnSettings().aoData[ aPos[0] ]._aData;
/* Link a column to it's correct ID for jeditable! */
if(aPos[1] == 1){
settings.submitdata = {id: aData[0],type:"ul"};
}else if(aPos[1] == 2){
settings.submitdata = {id: aData[0],type:"home"};
}else if(aPos[1] == 0){
settings.submitdata = {id: aData[4],type:"group"};
}
return true;

},
"height": "24px"
} );

}
} );


} );
[/code]

Miles

And my server side script.

In the order segment, I took out the two columns I'm hiding as bSearchable: false also fails to work :(

Sorting & filtering still works. Just the pagination buttons come up as deactivated. Even when I declare it as true.

[code]<?php
/* MySQL connection */
include_once("./dbc.php");
/* Paging */
$sLimit = "";
if ( isset( $_GET['iDisplayStart'] ) )
{
$sLimit = "LIMIT ".$dbc->real_escape_string( $_GET['iDisplayStart'] ).", ".
$dbc->real_escape_string( $_GET['iDisplayLength'] );
}

/* Ordering */
if ( isset( $_GET['iSortCol_0'] ) )
{
$sOrder = "ORDER BY ";
for ( $i=0 ; $i<$dbc->real_escape_string( $_GET['iSortingCols'] ) ; $i++ )
{
$sOrder .= fnColumnToField($dbc->real_escape_string( $_GET['iSortCol_'.$i] ))."
".$dbc->real_escape_string( $_GET['iSortDir_'.$i] ) .", ";
}
$sOrder = substr_replace( $sOrder, "", -2 );
}

/* Filtering - NOTE this does not match the built-in DataTables filtering which does it
* word by word on any field. It's possible to do here, but concerned about efficiency
* on very large tables, and MySQL's regex functionality is very limited
*/
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE groups.name LIKE '%".$dbc->real_escape_string( $_GET['sSearch'] )."%' OR ".
"user_level.userlevel LIKE '%".$dbc->real_escape_string( $_GET['sSearch'] )."%' OR ".
"user_level.home LIKE '%".$dbc->real_escape_string( $_GET['sSearch'] )."%'";
}




$sQuery = "SELECT user_level.id, user_level.userlevel, user_level.home, user_level.groupid, groups.id AS \"g_id\", groups.name FROM user_level INNER JOIN groups ON user_level.groupid = groups.id $sWhere $sOrder $sLimit";
$rResult = $dbc->query($sQuery) or die($dbc->error());

$sQuery = "
SELECT FOUND_ROWS()
";
$rResultFilterTotal = $dbc->query( $sQuery) or die($dbc->error());
$aResultFilterTotal = $rResultFilterTotal->fetch_array();
$iFilteredTotal = $aResultFilterTotal[0];

$sQuery = "
SELECT COUNT(id)
FROM user_level
";
$rResultTotal = $dbc->query($sQuery) or die($dbc->error());
$aResultTotal = $rResultTotal->fetch_array();
$iTotal = $aResultTotal[0];

$sOutput = '{';
$sOutput .= '"sEcho": '.$_GET['sEcho'].', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
while ( $aRow = $rResult->fetch_assoc() )
{
$sOutput .= "[";
$sOutput .= '"'.addslashes($aRow['id']).'",';
$sOutput .= '"'.addslashes($aRow['name']).'",';
$sOutput .= '"'.addslashes($aRow['userlevel']).'",';
$sOutput .= '"'.addslashes($aRow['home']).'",';
$sOutput .= '"'.addslashes($aRow['g_id']).'"';
$sOutput .= "],";
}
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';

echo $sOutput;
function fnColumnToField( $i )
{
if ( $i == 0 )
return "id";
else if ( $i == 1 )
return "name";
else if ( $i == 2 )
return "userlevel";
else if ( $i == 3 )
return "home";
else if ( $i == 4 )
return "g_id";
}

?>
[/code]

Thanks in advance!

Miles

Okay I checked out the bit of code that was failing me.

[code]"fnUpdate": function ( oSettings, fnCallbackDraw )
{
if ( !oSettings.anFeatures.p )
{
return;
}

oSettings.nPrevious.className =
( oSettings._iDisplayStart === 0 ) ?
"paginate_disabled_previous" : "paginate_enabled_previous";

oSettings.nNext.className =
( oSettings.fnDisplayEnd() == oSettings.fnRecordsDisplay() ) ?
"paginate_disabled_next" : "paginate_enabled_next";
}
},
[/code]

Would appear that [code]oSettings.fnDisplayEnd() == oSettings.fnRecordsDisplay()[/code]

It is only returning 10 == 10 because it's server side. So it's pulling only the first 10 records to be displayed. Due to the limiting query.

A more detailed look into the fnDisplayEnd() function shows:

[code]this.fnDisplayEnd = function ()
{
if ( this.oFeatures.bServerSide ) {
return this._iDisplayStart + this.aiDisplay.length;
} else {
return this._iDisplayEnd;
}
};
[/code]

.length only returns the displayed records shown. Hmm, I need to think about this.

allan

Hi Miles,

Lots of things here :-)

1. The full_numbers issue is addressed in this post: http://datatables.net/forums/comments.php?DiscussionID=487&page=1#Item_1 . An annoying bug in 1.5.0, and will be fixed in the shortly to be released 1.5.1.

2. bSearchable in server-side processing is up to you to implement on the server-side. DataTables doesn't do any filtering in server-side processing, so it can't to anything with bSearchable. There is an assumption that the server-side script is tied to the table in question. If you want to pass extra information about which columns should be searchable to the server-side script, then you can add extra variables to the HTTP request: http://datatables.net/examples/server_side/custom_vars.html

3. The 10==10 issue. Yes, DataTables thinks that there are only 10 records in the current result set, therefore it disabled it's paging options. I think you are missing 'SQL_CALC_FOUND_ROWS' from your main query - which is required in order for FOUND_ROWS() to return the length of the full result set in MySQL. Have a look at the server-side processing demo: http://datatables.net/examples/server_side/server_side.html

Hope this helps.

Regards,
Allan

Miles

Man. How the hell did I miss that! I'm an idiot.

Saved my bacon there Allan! Donation coming your way!

Regards,

Miles