Search and Pagination not working while using server-side

narenverma

I am using the 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 is a several questions 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` ";

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?

allan

You haven't implemented any ORDER BY or WHERE in your server-side script. With server-side processing, it is the server-side script that is responsible for doing all the sorting.

Here are a few options:

1. If you have less than 50'000 rows, just disable the serverSide option and use client-side processing.
2. Update your PHP to add conditions for the ordering and filtering - see the parameters DataTables sends here.
3. Use our demo SSP class.
4. Use the Editor libraries.

Allan

narenverma

Yes, I have 30000k records in my database and it will increase. I tried SSP class but I am not able to use the where clause. I asked one question on Stack Overflow

https://stackoverflow.com/questions/63426594/where-clause-not-working-in-data-table-server-side-processing

colin

As Allan said, you can use WHERE clauses with the Editor library: https://editor.datatables.net/manual/php/conditions

Colin

narenverma

@colin, I am not using editor libraries, I am using now SSP class but not able to use the WHERE clause.

colin

See option 4 on Allan's list - this is open source, and provides the WHERE clause. If you want it in the SSP classes, you'll need to implement it there.

Colin

narenverma

@colin, Yes I checked option 4 but I don't want to use that Editor library. I have to use SSP classes and I tried that it's working perfectly but the main issue is where clause. I am not able to use the where clause. Even I asked question on Stack Overflow but no response. https://stackoverflow.com/questions/63426594/where-clause-not-working-in-data-table-server-side-processing