Moving from DOM to server-side: can not paginate with sFirst, sLast, sNext, sPrevious

afarber

Hello,

I'm trying to move from DOM (which worked fine, but very slow) to server-side processing.

I have the problem that my table displays the 20 records (as expected) and at the bottom it has the string "Quincy ids from 1 to 20 of 20 total (filtered from 977,632 total)" (why "of 20 total" here?) and the sFirst, sLast, sNext, sPrevious "buttons" are disabled.

Here is my HTML code:

[code]

var quincy_table = $("#quincy_table").dataTable( {
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"bAutoWidth": false,
"iDisplayLength": 20,
"aoColumns": [
/* qdatetime */ { "bSearchable": false },
/* id */ null,
/* name */ null,
/* category */ null,
/* appsversion */ null,
/* osversion */ null,
/* details */ { "bVisible": false },
/* devinfo */ { "bVisible": false, "bSortable": false }
],
"oLanguage": {
"sProcessing": "Wait please...",
"sZeroRecords": "No quincy ids found.",
"sInfo": "Quincy ids from _START_ to _END_ of _TOTAL_ total",
"sInfoEmpty": "Quincy ids from 0 to 0 of 0 total",
"sInfoFiltered": "(filtered from _MAX_ total)",
"sInfoPostFix": "",
"sSearch": "Search:",
"sUrl": "",
"oPaginate": {
"sFirst": "<<",
"sLast": ">>",
"sNext": ">",
"sPrevious": "<"
},
"sLengthMenu": 'Display ' +
'10' +
'20' +
'50' +
'100' +
'all' +
' quincy ids'
}
} );
[/code]

And here is my ajax.php:

[code]
$HEADERS = array(
'QDATETIME',
'ID',
'NAME',
'CATEGORY',
'APPSVERSION',
'OSVERSION',
'DETAILS', # hidden
'DEVINFO', # hidden
);

# sanity code checking sEcho being numeric etc. skipped here

$sth = $pg->prepare('select count(*) from quincyview');
$sth->execute();
if ($row = $sth->fetch(PDO::FETCH_NUM))
$data['iTotalRecords'] = $row[0];

$sql = sprintf('select %s from quincyview where %s order by %s %s offset %u limit %u',
join(',', $HEADERS),
join(' and ', $conditions),
$HEADERS[$iSortCol_0],
$iSortDir_0,
$iDisplayStart,
$iDisplayLength
);

$sth = $pg->prepare($sql);
$sth->execute($parameters);

$data['iTotalDisplayRecords'] = $sth->rowCount();

while ($row = $sth->fetch(PDO::FETCH_NUM)) {
array_push($aaData, $row);
}
$data['sEcho'] = $sEcho;
$data['aaData'] = $aaData;
print json_encode($data);
[/code]

Any ideas what is wrong here please?

Any advices on debugging?

I have donated a small amount, thank you
Alex

afarber

Also I wonder why { "sWidth": "100px" } is being ignored when I have longer strings in that column.

Is there some CSS attribute to be set to make the too long strings truncated?

Regards
Alex

GregP

I don't know about sWidth, other than to say that widths are almost always a "best guess" with HTML tables. Tables will use widths as general guidelines, but when the math for widths breaks elsewhere on the table, it can and WILL start ignoring what you've set.

As to the rest: the pagination issue and the "of 20" are related.

Your server-side script needs to return an iTotalDisplayRecords equal to the number of available records after filtering. Filtering does NOT include pagination, but rather just the remaining data set after search(es).

In your case, if you did NOT searches/filtering whatsoever, both iTotalRecords and iTotalDisplayRecords would have the value 977,632

It's a common mistake to think that "iTotalDisplayRecords" means the number of records you want the user to see; it really means, number of records available to be seen.

allan

As Greg notes, I think your iTotalDisplayRecords is where the problem lies. As the documentation says ( http://datatables.net/usage/server-side ) iTotalRecords and iTotalDisplayRecords should be the same unless the result set has been filtered. If DataTables needed to get the size of the returned data set, it would just do aaData.length rather than require it as a separate parameter.

> { "sWidth": "100px" }
> Is there some CSS attribute to be set to make the too long strings truncated?

If the string is wider than 100px, then the browser will reflow the table to allow the string to fit - this is just how table display in browsers works. If you want, you could wrap the text in DIV elements and use text-overflow: ellipsis; to truncate the strings, or even overflow:hidden on older browsers.

Allan