Pagination don't work by server side
Pagination don't work by server side
Hello!
I know this is an old shoe, but no answer in earlier questions helps me to solve my problem.
The story is nearly the same as in other similar questions. It is the first time I use DataTables and my knowledge level is not so high.
I have 1,000 rows (I've reduced for tests my data, regulary it has over 100,000 rows) and in the DataTable I got all rows even though Paging is activated. I see 20 Pages and my pagingLength is 50.
Excerpt HTML-Page
<script>
$(document).ready(function() {
$('#searchFailure').DataTable({
ajax: {
type: 'POST',
url: './searchfailure_getdata.php',
dataSrc: 'data',
data: {
FTY: <?php echo (isset($_GET['FTY'])) ? $_GET['FTY'] : 0; ?>
}
},
autoWidth : true,
serverSide: true,
responsive : true,
deferRender : true,
processing : true,
paging : true,
pageLength : 50,
searching : true,
searchable: true,
info : true,
ordering : true,
lengthMenu: [
[50, 100, 500, -1],
[50, 100, 500, 'Alle']
],
pagingType: 'full_numbers',
cache: true,
rowGroup: {
dataSrc: 24,
startRender: function(rows, group) {
return $('<tr class="group group-start"><td colspan="25">' + group + '</td></tr>');
}
}
});
});
</script>
searchfailure_getdaten.php
<?php
session_start();
include '../class.general.php';
$app= new app();
$pm_db = new db('app_pm');
$requestData = $_REQUEST;
$fty = $_POST['FTY'] ?? 38;
$columns = array(
0 => 'ID',
1 => 'FTY',
2 => 'SearchNo',
3 => 'Amount',
4 => 'CustomerNo',
5 => 'CustomerName',
6 => 'CustomerGroup',
7 => 'CustomerCC',
8 => 'CCPreferation',
9 => 'FTYPreferation',
10 => 'FPCC',
11 => 'FPGross',
12 => 'FPDisc1',
13 => 'FPRab2',
14 => 'FPRab3',
15 => 'FPNet',
16 => 'FPDate',
17 => 'RG',
18 => 'MLI',
19 => 'Bonus',
20 => 'Description',
21 => 'Created',
22 => 'Hint',
23 => 'ArticleCreated',
24 => 'SearchGroup'
);
$sqlBase = "SELECT * FROM vw_searchfailure WHERE FTY = $fty";
if(isset($requestData['search'][0])){
$sqlFilter = " AND (SearchNo LIKE '%" . $requestData['search'][0] . "%' OR
CustomerNo = " . $requestData['search'][0] . " OR
CustomerName LIKE '%" . $requestData['search'][0] . "%' OR
Description LIKE '%" . $requestData['search'][0] . "%')";
} else {
$sqlFilter = "";
}
if(!empty($requestData['order'][0]['column'])){
$sqlOrder = ' ORDER BY '.$column[$requestData['order']['0']['column']].' '.$requestData['order']['0']['dir'].' ';
} else {
$sqlOrder = ' ORDER BY SearchNo ASC, Created ASC';
}
$fetchData = $pm_db->query($sqlBase)->fetchAll();
$fetchDataFiltered = $pm_db->query($sqlBase . $sqlFilter . $sqlOrder)->fetchAll();
$totalRows = count($fetchData);
$filteredRows = count($fetchDataFiltered);
$data = array();
foreach($fetchDataFiltered as $row){
$nestedData = array();
$nestedData[] = $row['ID'];
$nestedData[] = $row['FTY'];
$nestedData[] = $row['SearchNo'];
$nestedData[] = $row['Amount'];
$nestedData[] = $row['CustomerNo'];
$nestedData[] = $row['CustomerName'];
$nestedData[] = $row['CustomerGroup'];
$nestedData[] = $row['CustomerCC'];
$nestedData[] = $row['CCPreferation'];
$nestedData[] = $row['FTYPreferation'];
$nestedData[] = $row['FPCC'];
$nestedData[] = $row['FPGross'];
$nestedData[] = $row['FPDisc1'];
$nestedData[] = $row['FPRab2'];
$nestedData[] = $row['FPRab3'];
$nestedData[] = $row['FPNet'];
$nestedData[] = $row['FPDate'];
$nestedData[] = $row['RG'];
$nestedData[] = $row['MLI'];
$nestedData[] = $row['Bonus'];
$nestedData[] = $row['Description'];
$nestedData[] = $row['Created'];
$nestedData[] = $row['Hint'];
$nestedData[] = $row['ArticleCreated'];
$nestedData[] = $row['SearchGroup'];
$data[] = $nestedData;
}
$json_data = array(
"draw" => intval($requestData['draw']),
"recordsTotal" => $totalRows,
"recordsFiltered" => $filteredRows,
"data" => $data
);
echo json_encode($json_data);
<?php
>
?>
What is wrong with this?
Thanks in advance for any help!!!
Best regards, Axel
Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.
This question has an accepted answers - jump to answer
Answers
Hi Alex,
You have pagination enabled on the client-side, but your PHP server-side script isn't doing any pagination. DataTables will sent
start
andlength
parameters, as shown in the documentation but your script isn't doing anything with them. You'd need a LIMIT and OFFSET in your SQL.What I would suggest you consider doing, is using Editor's PHP scripts for your server-side processing. You can make your own of course, but our Editor PHP libraries are MIT open source if you wanted to use a pre-built script.
Allan
Thanks...but now I have not understand the logic this.
I already thought so, but when I reduce the incoming data through LIMIT and OFFSET in the query, how can paging works, when datatable object gets only 50 rows? How the can know, that the data has over 100,000 rows
I think I missed the right place in the documentation.
You need to send back the
recordTotals
andrecordsFiltered
parameters, as noted in the docs I linked to before. They tell DataTables how many records there are in the data set, and this it can work out that paging from that.Allan
Good morning,
okay...but it does not work.
As you can see I modified the SQL so I got both parameter which you named, but the Paginator shows only 1.
As shown here it does work.
The question thus becomes what is different about the JSON data you are returning? I can see that your
recordsFiltered
is a string, and only has 50 as the value. It should be an integer, and unless you have a filter applied, it should be the same asrecordsTotal
.As the manual page I linked to says:
Allan
Very interesting, my last comment was not saved.
After I wrote it here, I saw it myself and already changed it to
intval($filteredRows);
.But nothing happened.
Now I have implemented LIMIT and OFFSET in my statement and it works as I thought it would at the beginning.
The data array has only 25 rows, but the variable $totalRows has 1,000 rows. The Paginator shows me only the 1, because the data array has only 25 rows.
Here my code:
My HTML-Script for Data Tables, that I have reduced to minimum.
The info-Block tolds me that the data has 1,000 rows in total, but shows me only 10 rows and only Page 1 as Paginator.
Is this correct, that after I edit my new comment, that the comment is missing now?
The spam filter blocked it, it's there now!
Colin
That suggests that
$filteredRows
is 10 (or 25, or whatever your page length is). DataTables doesn't need you to tell it how long yourdata
array is - it can trivially dodata.length
for that.What it needs to know is how many records are in the full data set, both with filtering and without.
If the table is NOT filtered, then
recordsFiltered
andrecordsTotal
should be identical, as I mentioned above.If the table has filtering applied (i.e. the user has typed in a search term), then the two might be different. Also
recordsFiltered
might be the same length as the number or records, but it might not.You need three queries for server-side processing:
Allan
In fact, checking the code, I can see that is exactly what the issue is.
Where
$fetchDataFiltered
is the paged data. That is not whatrecordsFiltered
is (see above and the manual).Allan
I had already considered that.
I have a Base Statement without user specified WHERE / ORDER BY / LIMIT and OFFSET and one with all elements.
My data has over 1,000,000 rows in total. An user opens the page and select a base filter (=> FTY). This is the base Statement for all.
Now a user can specify further conditions.
Nothing change if I remove the WHERE FTY = $fty ;-)
You currently have two queries there. You need three.
If there is no filter applied, the response should be something like:
Is wrong, as I stated above.
Allan
That knocks my socks off :-D
Only a small modification of one row helps me.
Many thanks Allan
I think that is still wrong I'm afraid.
Consider the case where you have a simple filter - you might end up with the case where:
In such a case the JSON reply will be:
However, your change will make
recordsFiltered
the same length as the data for the selected page - e.g. 10.You honestly do need three queries to handle this.
Allan