Pagination don't work by server side

sbhog

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.

allan

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 and length 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

sbhog

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.

allan

You need to send back the recordTotals and recordsFiltered 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

sbhog

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.

allan

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 as recordsTotal.

As the manual page I linked to says:

recordsFiltered integer Total records, after filtering (i.e. the total number of records after filtering has been applied - not just the number of records being returned for this page of data).

Allan

sbhog

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:

$fetchData = $pm_db->query($sqlBase)->fetchAll();
$fetchDataFiltered = $pm_db->query($sqlBase . $sqlFilter . $sqlOrder . $sqlLimit)->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" => intval($totalRows),
    "recordsFiltered" => intval($filteredRows),
    "data" => $data
);

echo json_encode($json_data);

My HTML-Script for Data Tables, that I have reduced to minimum.

  <script>
    $(document).ready(function() {
      $('#searchFailure').DataTable({
        ajax: {
          type: 'POST',
          url: './searchfailure_getdata.php',
          data: {
            FTY: <?php echo (isset($_GET['FTY'])) ? $_GET['FTY'] : 0; ?>
          }
        },
        serverSide: true,
        processing : true,
        paging     : true
      });
    });
  </script>

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.

sbhog

Is this correct, that after I edit my new comment, that the comment is missing now?

colin

The spam filter blocked it, it's there now!

Colin

allan

That suggests that $filteredRows is 10 (or 25, or whatever your page length is). DataTables doesn't need you to tell it how long your data array is - it can trivially do data.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 and recordsTotal 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:

1. Get the paged, ordered and searched data
2. Get the number of rows with search applied (no limit / offset)
3. Get the number of rows with no search applied.

Allan

allan

In fact, checking the code, I can see that is exactly what the issue is.

$filteredRows = count($fetchDataFiltered);

Where $fetchDataFiltered is the paged data. That is not what recordsFiltered is (see above and the manual).

Allan

sbhog

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.

$sqlBase = "SELECT * FROM vw_searchfailure WHERE FTY = $fty";

$fetchData = $pm_db->query($sqlBase)->fetchAll();
$fetchDataFiltered = $pm_db->query($sqlBase . $sqlFilter . $sqlOrder . $sqlLimit)->fetchAll();

$totalRows = count($fetchData);
$filteredRows = count($fetchDataFiltered);

sbhog

Nothing change if I remove the WHERE FTY = $fty ;-)

allan

You currently have two queries there. You need three.

If there is no filter applied, the response should be something like:

{
  "draw": 1,
  "data": [ ... ],
  "recordTotals": 1000000,
  "recordsFiltered": 1000000
}

$filteredRows = count($fetchDataFiltered);
...
"recordsFiltered" => intval($filteredRows),

Is wrong, as I stated above.

Allan

sbhog

That knocks my socks off :-D

Only a small modification of one row helps me.

$filteredRows = ($hasFilter) ? count($fetchDataFiltered) : $totalRows;

Many thanks Allan

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:

1. Data set has 1000 records
2. 500 records match the filter
3. 10 records are displayed.

In such a case the JSON reply will be:

{
  "draw": 1,
  "data": [ ... ],
  "recordTotals": 1000,
  "recordsFiltered": 500
}

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