Pagination in server-side does not work correctly

damianricobelli

Hi. I'm having a problem generating a datatable implementing server-side.

Everything works fine, but when I click on the next page, it throws me the error "DataTables warning: table id = DataTables_Table_1 - Invalid JSON response .For more information about this error, please see http://datatables.net/tn/1 "

I have 3 pages. If I go to 1 or 3 it works fine, but 2 does not work, and I do not understand why.

**Table HTML: **

<table class="table table-bordered table-hover dt-responsive tablaPuestos" width="100%">

  <thead>

   <tr>

     <th style="width:10px">ID</th>
     <th>Nombre</th>             
     <th>Value</th>

   </tr> 

  </thead>

</table>

JS:

$('.tablaPuestos').DataTable({
      'processing': true,
      'serverSide': true,
      'serverMethod': 'post',
      'ajax': {
          'url':'ajax/tablaPuestos.ajax.php',
      },
      'columns': [
         { data: 'id' },
         { data: 'nombre' },
         { data: 'value' },
      ]
});

PHP:

<?php
include 'connection.php';

## Read value
$draw = $_POST['draw'];
$row = $_POST['start'];
$rowperpage = $_POST['length']; // Rows display per page
$columnIndex = $_POST['order'][0]['column']; // Column index
$columnName = $_POST['columns'][$columnIndex]['data']; // Column name
$columnSortOrder = $_POST['order'][0]['dir']; // asc or desc
$searchValue = $_POST['search']['value']; // Search value

$searchArray = array();

## Search 
$searchQuery = " ";
if($searchValue != ''){
   $searchQuery = " AND (id LIKE :id or 
        nombre LIKE :nombre OR 
        value LIKE :value ) ";
   $searchArray = array( 
        'id'=>"%$searchValue%", 
        'nombre'=>'%$nombre%',
        'value'=>'%$searchValue%'
   );
}

## Total number of records without filtering
$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM puestos ");
$stmt->execute();
$records = $stmt->fetch();
$totalRecords = $records['allcount'];

## Total number of records with filtering
$stmt = $conn->prepare("SELECT COUNT(*) AS allcount FROM puestos WHERE 1 ".$searchQuery);
$stmt->execute($searchArray);
$records = $stmt->fetch();
$totalRecordwithFilter = $records['allcount'];

## Fetch records
$stmt = $conn->prepare("SELECT * FROM puestos WHERE 1 ".$searchQuery." ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset");

// Bind values
foreach($searchArray as $key=>$search){
   $stmt->bindValue(':'.$key, $search,PDO::PARAM_STR);
}

$stmt->bindValue(':limit', (int)$row, PDO::PARAM_INT);
$stmt->bindValue(':offset', (int)$rowperpage, PDO::PARAM_INT);
$stmt->execute();
$empRecords = $stmt->fetchAll();

$data = array();

foreach($empRecords as $row){
   $data[] = array(
      "id"=>$row['id'],
      "nombre"=>$row['nombre'],
      "value"=>$row['value']
   );
}

## Response
$response = array(
   "draw" => intval($draw),
   "recordsTotal" => $totalRecords,
   "recordsFiltered" => $totalRecordwithFilter,
   "data" => $data
);

echo json_encode($response);

Connection.php

<?php

$server = "localhost";
$username = "root";
$password = "";
$dbname = "gestor-futbol";

// Create connection
try{
   $conn = new PDO("mysql:host=$server;dbname=$dbname","$username","$password");
   $conn->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
}catch(PDOException $e){
   die('Unable to connect with the database');
}

kthorngren

The place to start is to follow the steps outlined at the link provided in the alert message:
http://datatables.net/tn/1

Let us know what you find.

Kevin

damianricobelli

@kthorngren It's exactly what I did. The answer given to me on pages 1 and 3, are of this style: {"draw": 1, "recordsTotal": "30", "recordsFiltered": "30", "data": [{"id": "1", "name": "Archer", "value": "archer"}, ........}]}

But on page two nothing comes at all

damianricobelli

@kthorngren In the header of the response network on the page containing the error, the following comes (but I think no problem is reflected)

draw: 2
columns[0][data]: id
columns[0][name]: 
columns[0][searchable]: true
columns[0][orderable]: true
columns[0][search][value]: 
columns[0][search][regex]: false
columns[1][data]: nombre
columns[1][name]: 
columns[1][searchable]: true
columns[1][orderable]: true
columns[1][search][value]: 
columns[1][search][regex]: false
columns[2][data]: value
columns[2][name]: 
columns[2][searchable]: true
columns[2][orderable]: true
columns[2][search][value]: 
columns[2][search][regex]: false
order[0][column]: 0
order[0][dir]: asc
start: 10
length: 10
search[value]: 
search[regex]: false

kthorngren

But on page two nothing comes at all

You will need to look at your server script to see why nothing is returned.

Kevin

damianricobelli

@kthorngren In the end, the problem was as follows: I had configured the database in utf8mb4_spanish_ci, and apparently the whole process must be done in 1latin_. I made the change and everything works correctly