Search and Pagination not working while using server-side

Question

I am using the data table server-side. I am getting the records using ajax. My issue is, search and pagination not working. I am getting the search and pagination along with all the data.

Please check the below image, I am showing 10 records per page but it is showing all.

I checked on StackOverflow there are server question asked on this topic. I almost checked every question but still, I am not able to find the solution.

I am using below code

if($_REQUEST['action']=='adminList'){

$stmt = $pdo->prepare("SELECT count(*) FROM tbl_admin");
$stmt->execute();
$totalRecords = $stmt->fetchColumn();
$query="SELECT `admin_id`, `a_firstname`, `a_lastname`, `a_email`,  `date_of_created` FROM `tbl_admin` WHERE is_active = 1 order by date_of_created DESC";

try {
      $stmt = $pdo->prepare($query);
      $stmt->execute();
      $result = $stmt->fetchAll();
             
      $data['data'] = [];
      foreach ($result as $row) {

        $arr_result = array(
                    //"id" =>$i++,
                    "name" =>$row['a_firstname'].' '.$row['a_lastname'],
                    "email" => $row['a_email'],
                    "date_of_created" => $row['date_of_created'],
        );


        $data['data'][] = $arr_result;
                }

                
                }
                catch(PDOException $e) {
                    echo "Error: " . $e->getMessage();
                }

$json_data = array(  
"draw"=> intval( $_REQUEST['draw'] ),
"recordsTotal"    => intval($totalRecords),  
"recordsFiltered" => intval($totalRecords),
"data"            => $data['data']
);

// echo "<pre>";
 //print_r($json_data);
echo json_encode($json_data);
//exit();
}

Js

$(document).ready(function() {
  var dataTable = $('#adminList').DataTable({
    "processing": true,
    "serverSide": true,
    "paging": true,
    "searchable": true,
    "ajax": {
      url: "fetch.php",
      type: "post",
      data: {
        action: "adminList"
      }
    },
    language: {
      sLengthMenu: "Show _MENU_", // remove entries text
      searchPlaceholder: "Search",
      emptyTable: "No record found",
      search: ""
    },
    "pageLength": 10,
    "paging": true,
    "columns": [{
        "data": "name"
      },
      {
        "data": "email"
      },
      {
        "data": "date_of_created"
      }
    ]
  });
});

This is my output

Array
(
    [draw] => 1
    [recordsTotal] => 17
    [recordsFiltered] => 17
    [data] => Array
        (
    // getting my all records
)
)

Can anyone help me out what is the issue with my code?

There is some (old) sample code on https://legacy.datatables.net/examples/data_sources/server_side.html. For paging look at `if ( isset( $_GET['iDisplayStart'] ) && $_GET['iDisplayLength'] != '-1' )` as your code always fetches all of the data (not LIMIT in your SQL) — Nigel Ren, Aug 12 '20 at 15:30
@NigelRen, But where I have to add that code? I believe that coming from the data table plugin. — user9437856, Aug 12 '20 at 15:33
@NigelRen, If I use LIMIT in my query then I will get only limited value..right? I have to show all the records and I have to display 10 records per page — user9437856, Aug 12 '20 at 15:36
LIMIT allows you to say which set of records to return - have a look at [Pagination using MySQL LIMIT, OFFSET](https://stackoverflow.com/questions/20364349/pagination-using-mysql-limit-offset) — Nigel Ren, Aug 12 '20 at 15:38
@NigelRen, I set LIMIT 10 in my query. So now I am getting only 10 records but what about other records? I believe that the data table handles all this type of limit. — user9437856, Aug 12 '20 at 15:44
Check the serveside code of datatable here and implement it accordingly: https://github.com/pandeyz/Jquery-Datatable-Server-Side-with-Custom-Inline-Editing — Mayank Pandeyz, Aug 17 '20 at 07:11
@MayankPandeyz, Can you check my code once? My pagination is working but I am getting all the records. — user9437856, Aug 17 '20 at 07:15

score 0 · Answer 1 · answered Aug 14 '20 at 18:51

0

PROBLEM

You have enabled server-side processing mode with serverSide: true which requires you to handle searching, ordering and pagination performed on the server-side by your script. However your script returns all the data, that's why you see all results and not just first page.

SOLUTION

Write proper server-side handling code or use helper libraries.

For example, DataTables distribution includes ssp.class.php helper calss and sample script to aid in generating response if you're using PHP.

Otherwise, you can inspect sent parameters and paginate your results based on start and length request parameters.
Switch to client-side processing mode by removing serverSide: true as your initialization option and return all data in your server-side response. Please keep in mind that this solution is not ideal if you have a large dataset (>10,000 records).

answered Aug 14 '20 at 18:51

Gyrocode.com

57,606
14
150
185

Thanks for the answer, Let me implement this – user9437856 Aug 15 '20 at 05:49
Regarding ssp.class.php. One month ago I asked on the question but I haven't got solution https://stackoverflow.com/questions/62708059/datatables-server-side-processing-not-displaying-the-output-on-page-and-where-co – user9437856 Aug 15 '20 at 05:54
I tried lengthMenu:[[3,25,50,100,-1],[3,25,50,100,"All"]], "pageLength":3,but still my all records are displaying. – user9437856 Aug 15 '20 at 08:43
I removed all the code and refer this link https://datatables.net/examples/server_side/simple.html. Now I am getting the output. But I this issue https://stackoverflow.com/questions/63426594/where-clause-not-working-in-data-table-server-side-processing – user9437856 Aug 16 '20 at 17:17

Search and Pagination not working while using server-side

1 Answers1

PROBLEM

SOLUTION

Linked