Pagination doesn't work with server-side processing

yuval

Hey,

First, this is a great plugin!
I have an issue with pagination and server-side processing. Everything works just fine, and with 'bServerSide: false' pagination also works. However, for 'bServerSide: true', pagination doesn't work (sPaginationType is set for 'full_numbers' but all the buttons are grayed out...). Here's the function that I use:
[code]
$(document).ready(function() {
$('#maintable').dataTable({
"iDisplayLength": 25,
"bFilter": true,
"bAutoWidth": true,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"bProcessing": false,
"bServerSide": true,
"aaSorting": [[ 2, "desc" ],[ 3, "desc" ] ],
"aoColumns": [
{ "sTitle": "Script"},
{ "sTitle": "File"},
{ "sTitle": "Error"},
{ "sTitle": "Date"}
],
"sAjaxSource": "ajax_server.php"
});
});
[/code]

Note that when I set "iDisplayStart": 40, the first 2 pages buttons were enabled, but when I clicked on the first page, all the buttons were disabled.

Thanks,
Yuval

allan

bServerSide true / false are significantly different in what they expect from the server - which is it that you want? : http://datatables.net/usage/#data_sources

Allan

modernclix

It seems that you have bServerSide enabled but you are not dealing with ajax calls to "ajax_server.php". Each time you click on a page number, press the sort buttons, make a search, etc. a call to 'ajax_server.php' is made, so your php script need to address all that information to work properly. This is explained here: http://datatables.net/examples/data_sources/server_side.html

Maybe, as Allan says, you don't want that. If you are dealing with small amount of records (less than 1000) and it works fine with Server Side disabled, then you probably don't need to implement it.

yuval

Hi,

Thanks for your replies. Maybe my initial post was not clear enough...
Anyway, I want the server to take care of filtering/ paging/ sorting , ie, bServerSide = true. I think that my ajax call is fine since Searching (filtering), Limiting and Sorting work fine (checked with Live HTTP header, and ajax calls are made).
The only thing that doesn't work for my is pagination (buttons are disabled).
While debugging this, I set "iDisplayStart": 40 (where "iDisplayLength": 25 and total=1000) and this enabled 'First', 'Previous', '1' and '2' buttons (since page 2 was initially loaded) but '3', 'Next' and 'Last' were still disabled. After selecting page '1', all buttons were grayed out.
Hope it makes sense. Thanks,
Yuval

allan

Oh I see - thanks for the clarification. In that case I would guess that the value you are returning for "iTotalDisplayRecords" and or "iTotalRecords" are the number of records that are being displayed rather than the total number in the table: http://datatables.net/usage/server-side

Allan

yuval

That was fast :-)

It looks like the values are ok:
[code]
{"sEcho":0, "iTotalRecords": 1000, "iTotalDisplayRecords": 1000, "aaData": [ [ "Script1","File1","Error1",""],[ "Script2","File2","Error2",""],[ "Script3","File3","Error3",""],[ "Script4","File4","Error4",""],[ "Script5","File5","Error5",""],....
[/code]

Here's the PHP pagination code:

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

Yuval

modernclix

I think you misunderstood the meaning of iTotalRecords and iTotalDisplayRecords, maybe this example could help you:

iTotalRecords:

[code]SELECT COUNT(id) FROM USERS[/code]

iTotalDisplayRecords:

[code]SELECT COUNT(id) FROM USERS WHERE name LIKE '%John%'[/code]

And the query for pagination:

[code]
$start = $_REQUEST['iDisplayStart'];
$amount = $_REQUEST['iDisplayLength']

$query = "SELECT * FROM USERS WHERE NAME LIKE '%John%' LIMIT $start,$amount";
$result = mysql_query($query);
$iTotalDisplayRecords = mysql_num_rows($result);
[/code]

But in your example you have 1000 as itotalRecords and 1000 as iTotalDisplayRecords so it will always show 1000 records without pagination.

You need to make three queries:

Nº 1: for iTotalRecords, this is counting all the records without filtering and without paging (no use of LIMIT)
Nº 2: for iTotalDisplayRecords, this is counting all the records WITH filtering and without paging

Nº 3: the last one that is the final query that will show the result, this one is same as Nº 2 with the use of LIMIT as shown in the last example.

allan

In addition to modernclix's comments your 'sEcho:0' looks dodgy and probably won't help. It needs to be the value that DataTables sends. Worth having a poke around the example with Firebug to see what is being sent and received: http://datatables.net/examples/data_sources/server_side.html

Allan

yuval

phh.. good one :-S...

Well, the problem was with this sql statement:
[code]
$selstring = "SELECT FOUND_ROWS()";
[/code]
After I changed it to:
[code]
$selstring = "SELECT COUNT(id)
FROM $file
$sWhere";
[/code]

it worked. BTW, in both cases, initial Json object has itotalRecords = iTotalDisplayRecords = 1000.
The problem was that I used FOUND_ROWS() but for the main SQL statement I didn't use SQL_CALC_FOUND_ROWS. From MySQL documentation:
"To obtain this row count, include a SQL_CALC_FOUND_ROWS option in the SELECT statement, and then invoke FOUND_ROWS() afterward"

After I added SQL_CALC_FOUND_ROWS my original code started to work :-)

Thanks for your help,
Yuval