Server side processing works only when I have a few data

simotux

Hello,

i'm working about this tutorial https://coderexample.com/datatable-demo-server-side-in-phpmysql-and-ajax/ and that script works without any problem in my testing environment with 50 rows.

I used the same script in my production environment but it doesn't show 2000 rows and error_log said that index is undefined. I tried to force the limit to 0 to 700, for example, and it shows 700 rows. I don't know if i miss something in my js script or something else... thank you!

Query doesn't fill the ORDER BY when i don't limit to 700

$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";

It shows this:

string(107) "SELECT id,email,nome,cognome,lingua,unsubscribe FROM newsletter_utenti WHERE 1=1 ORDER BY LIMIT , "

/var/log/httpd/error_log

[Wed Oct 19 14:55:37.724942 2016] [:error] [pid 28440] [client xxxxxx] PHP Notice:  Undefined index:  in search_user.php on line 31, referer: search_user.php
[Wed Oct 19 14:55:37.724958 2016] [:error] [pid 28440] [client xxxxxx] PHP Notice:  Undefined index: order in search_user.php on line 31, referer: search_user.php
[Wed Oct 19 14:55:37.724971 2016] [:error] [pid 28440] [client xxxxxx] PHP Notice:  Undefined index: start in search_user.php on line 31, referer: search_user.php
[Wed Oct 19 14:55:37.724983 2016] [:error] [pid 28440] [client xxxxxx] PHP Notice:  Undefined index: length in search_user.php on line 31, referer: search_user.php

JS script

var table = $('#employee-grid').DataTable( {
    "processing": true,
    "serverSide": true,
    "order": [[ 0, "desc" ]],
    "ajax":{
      url : api_ricercautente_nl_server,
      type: "POST",
      error: function(){
        $(".employee-grid-error").html("");
        $("#employee-grid").append('<tbody class="employee-grid-error"><tr><th colspan="3">No data found in the server</th></tr></tbody>');
        $("#employee-grid_processing").css("display","none");
      }
    },
    "scrollY": "300px",
    "scrollCollapse": true
  } );

PHP

<?php
require "common.php" ;
$requestData= $_REQUEST;
$columns = array(
    0 =>'id',
    1 =>'email',
    2 =>'nome',
    3 =>'cognome',
    4 =>'lingua',
    5 =>'unsubscribe'
);
$sql = "SELECT id,email,nome,cognome,lingua,unsubscribe FROM newsletter_utenti";
$query=mysqli_query($db, $sql) or die();
$totalData = mysqli_num_rows($query);
$totalFiltered = $totalData;

$sql = "SELECT id,email,nome,cognome,lingua,unsubscribe ";
$sql.=" FROM newsletter_utenti WHERE 1=1";
if( !empty($requestData['search']['value']) ) {
    $sql.=" AND ( id LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR email LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR nome LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR cognome LIKE '".$requestData['search']['value']."%' ";
    $sql.=" OR lingua LIKE '".$requestData['search']['value']."%' )";
}
$query=mysqli_query($db, $sql) or die("test");
$totalFiltered = mysqli_num_rows($query);
$sql.=" ORDER BY ". $columns[$requestData['order'][0]['column']]."   ".$requestData['order'][0]['dir']."  LIMIT ".$requestData['start']." ,".$requestData['length']."   ";
//$sql.=" ORDER BY id desc LIMIT 0,700   ";
$query=mysqli_query($db, $sql) or die("errore order by");

$data = array();
while( $row=mysqli_fetch_array($query) ) {
    $nestedData=array();
    $nestedData[] = $row["id"];
    $nestedData[] = $row["email"];
    $nestedData[] = $row["nome"];
    $nestedData[] = $row["cognome"];
    $nestedData[] = $row["lingua"];
    $nestedData[] = $row["unsubscribe"];
    $data[] = $nestedData;
}
$json_data = array(
    "draw"            => intval( $requestData['draw'] ),
    "recordsTotal"    => intval( $totalData ),
    "recordsFiltered" => intval( $totalFiltered ),
    "data"            => $data
);

echo json_encode($json_data);
?>

simotux

Solved!

The MySQL instance is not configured to expect UTF-8 encoding by default from client connections, so I used SET NAMES utf8.

Thank you all!