Server-side shows all rows at once

DzejCi

Hi,
I'm trying to use server-side with Datatable in my project. From SQL I receive 40k rows, and I would like to load only (for example) 100 at once.
My PHP script:

include('db.php');

     // DataTables request
     $requestData = $_POST;
     $primaryKey = 'id';
     $columns = array(
     array( 'db' => 'id',          'dt' => 0 ),
     array( 'db' => 'firstname',   'dt' => 1 ),
     array( 'db' => 'lastname',    'dt' => 2 ),
     array( 'db' => 'email',       'dt' => 3 ),
     array( 'db' => 'phone',       'dt' => 4 ),
     array( 'db' => 'points',      'dt' => 5 ));

     // Data output
     $data = array();
     
     $sql = "SELECT COUNT(*) as totalRecords FROM emp WHERE a = 0 AND b = 6";
     $stmt = sqlsrv_query($conn, $sql);
     if($stmt === false){
          die(print_r(sqlsrv_errors(), true));
     }
     $row = sqlsrv_fetch_array($stmt);
     $totalRecords = $row['totalRecords'];
     
     $sql = "SELECT " . implode(", ", array_column($columns, 'db')) . " FROM emp WHERE a = 0 AND b = 6";
     $stmt = sqlsrv_query($conn, $sql);
     if($stmt === false){
          die(print_r(sqlsrv_errors(), true));
     }
     while ($row = sqlsrv_fetch_array($stmt) ) {
          $data[] = $row;
     }

     // Output in DataTables format
     $output = array(
          "draw" => intval($requestData['draw']),
          "recordsTotal" => intval($totalRecords),
          "recordsFiltered" => intval($totalRecords),
          "data" => $data
     );
 
     echo json_encode($output);

and my "view" code:

<table id="points_table" class="table dataTable no-footer" style="width: 100%;" role="grid" aria-describedby="datatable_info">
<thead>
<tr role="row">
<th>#</th>
<th>Name</th>
<th>Lastname</th>
<th>E-mail</th>
<th>Phone</th>
<th>Points</th>
</tr>
</thead>
</table>

<script>
$(document).ready(function() {
     $('#points_table').DataTable({
          processing: true,
          serverSide: true,
          ajax: {
               url: '/app/discounts/sql_helper.php',
               type: 'POST'
          },
          paging: true,
          pageLength: 50
     });
});
</script>

Problem is that I receive one huge table without pagination with all 40k rows at once. Can you please help?

kthorngren

I'm not proficient with PHP but it doesn't look like your script supports the Server Side Processing protocol. Essentially you need to use SQL OFFSET and LIMIT based on the paging information sent to limit the rows to the page being displayed.

This blog explains how to use the server side Editor libraries for server side processing. You may want to use these scripts for your server side processing to work.

Kevin