datatables Server Side Data Processing Get JSON data from MySQL table

Example

On the official website of DataTable is an example of how a server-side process with PHP and MySQL can look. This example is deprecated and can no longer be used with PHP 7 (the function "mysql_pconnect" and the associated functions are deprecated, see this post).

So this function gives you a wellformed JSON data as response:

<?php
//include database connection file
$servername = "localhost";
$username = "root";
$password = "root";
$dbname = "name";

$conn = mysqli_connect($servername, $username, $password, $dbname) or die("Connection failed: " . mysqli_connect_error());

/* check connection */
if (mysqli_connect_errno()) {
  printf("Connect failed: %s\n", mysqli_connect_error());
  exit();
}

// initilize all variable
$params = $columns = $totalRecords = $data = array();
$params = $_REQUEST;
//define index of column name
$columns = array(
    0 =>'id',
    1 =>'name',
    2 =>'salery',
);

$where = $sqlTot = $sqlRec = "";

// check search value exist
if( !empty($params['search']['value']) ) {
    $where .=" WHERE ";
    $where .=" ( id LIKE '".$params['search']['value']."%' ";
    $where .=" OR name LIKE '".$params['search']['value']."%' ";
    $where .=" OR salery LIKE '".$params['search']['value']."%' )";
}

// getting total number records without any search
$sql = "SELECT * FROM `employees` ";
$sqlTot .= $sql;
$sqlRec .= $sql;

//concatenate search sql if value exist
if(isset($where) && $where != '') {
    $sqlTot .= $where;
    $sqlRec .= $where;
}

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

$queryTot = mysqli_query($conn, $sqlTot) or die("database error:". mysqli_error($conn));

$totalRecords = mysqli_num_rows($queryTot);

$queryRecords = mysqli_query($conn, $sqlRec) or die("error to fetch employees data");

while( $row = mysqli_fetch_row($queryRecords) ) {
    $data[] = $row;
}

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

echo json_encode($json_data);  // send data as json format
?>

The response looks like and can then be processed by the DataTable:

{
  "draw": 1,
  "recordsTotal": 3,
  "recordsFiltered": 2,
  "data": [
      {
        "id":"1",
        "name":"Jim",
        "salery":"1000"
      },
      {
        "id":"2",
        "name":"Claudia",
        "salery":"3000"
      },
      {
        "id":"3",
        "name":"Tommy",
        "salery":"2000"
      }
  ]
}