Pagination not working

Pagination not working

MilesMiles Posts: 12Questions: 0Answers: 0
edited August 2009 in General
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]

Replies

  • MilesMiles Posts: 12Questions: 0Answers: 0
    edited August 2009
    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!
  • MilesMiles Posts: 12Questions: 0Answers: 0
    edited August 2009
    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.
  • allanallan Posts: 61,446Questions: 1Answers: 10,054 Site admin
    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
  • MilesMiles Posts: 12Questions: 0Answers: 0
    edited August 2009
    Man. How the hell did I miss that! I'm an idiot.

    Saved my bacon there Allan! Donation coming your way!

    Regards,

    Miles
This discussion has been closed.