searchpanes API php serverSide: true

rwnukiewicz

My problem is that when I use the filter in searchpanes, my data in datatable is not sorted, but if I use my data from ajax.php response and copy the array results to a json file and set serverSide to false, everything works fine. The data in datatable sorts and works with searchpanes.

What is wrong. Who can help my. Why searchpanes is not work with data in datatable when serverSide is set true.

/* My code */
let table = $('#example').DataTable({
lengthMenu: [[10, 50, 100, 200, 300, 600], ["10", "50", "100", "200", "300", "600"]],
processing: true,
deferRender: true,
serverMethod: 'POST',
select: true,
serverSide: true,
ajax: {
url: 'ajax.php',
},
columnDefs: [
{
searchPanes: {
show: true
},
targets: [0, 1, 2, 3]
}
],
columns: [
{ data: "zl_zlecenia_numer" },
{ data: "zl_nr_zam_dostawcy" },
{ data: "id_kontrahenta" },
{ data: "zl_user_register" }
],
searchPanes: {
viewTotal: true,
columns: [0, 1, 2, 3]
},
dom: 'Plfrtip',
initComplete: function (settings, json) {
if (json.searchPanes && json.searchPanes.options) {
          $.each(json.searchPanes.options, function (column, data) {
           table.searchPanes.container().find('.dtsp-' + column).empty();
                $.each(data, function (index, option) {
                    table.searchPanes.container().find('.dtsp-' + column).append('<option value="' + option.value + '">' + option.label + '</option>');
                });
            });
            table.searchPanes.rebuildPane(); 
        } else {
            console.log('No data SearchPanes from Ajax.');
        }            
    }, 

/* My ajax.php code */
try {
     $db = new PDO("mysql:host=".DBHOST.";charset=".DBCHARSET.";dbname=".DBNAME, DBUSER, DBPASS);
     $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
     $db->setAttribute(PDO::ATTR_EMULATE_PREPARES, false);
     $db->setAttribute(PDO::MYSQL_ATTR_INIT_COMMAND, "SET NAMES 'utf8'");
     $db->exec("SET NAMES 'utf8';");
 } catch(PDOException $e) {
     echo '<p class="bg-danger">'.$e->getMessage().'</p>';
     exit;
 }
$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();
$searchQuery = " ";
if($searchValue != ''){
    $searchQuery = " AND (
        zl.zl_zlecenia_numer LIKE :zl_zlecenia_numer OR
        zl.zl_nr_zam_dostawcy LIKE :zl_nr_zam_dostawcy OR
        zl.zl_user_register LIKE :zl_user_register
        )";
    $searchArray = array(
        'zl_zlecenia_numer'=>"%$searchValue%",
        'zl_nr_zam_dostawcy'=>"%$searchValue%",
        'zl_user_register'=>"%$searchValue%"        
    );
}
$stmt = $db->prepare("SELECT COUNT(*) AS allcount FROM zl_zlecenia zl");
$stmt->execute();
$records = $stmt->fetch();
$totalRecords = $records['allcount'];
$stmt = $db->prepare("SELECT COUNT(*) AS allcount FROM zl_zlecenia zl WHERE 1 ".$searchQuery);
$stmt->execute($searchArray);
$records = $stmt->fetch();
$totalRecordwithFilter = $records['allcount'];

$query="SELECT id_zlecenia,zl_zlecenia_numer,zl_nr_zam_dostawcy,id_kontrahenta,zl_user_register FROM zl_zlecenia zl WHERE 1 ".$searchQuery."  ORDER BY ".$columnName." ".$columnSortOrder." LIMIT :limit,:offset";
$stmt = $db->prepare($query);
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();
$searchPanesData = array();
foreach($empRecords as $row)
{
    $data[] = array(
        'zl_zlecenia_numer' => $row['zl_zlecenia_numer'],
        'zl_nr_zam_dostawcy' => $row['zl_nr_zam_dostawcy'],
        'id_kontrahenta' => $row['id_kontrahenta'],
        'zl_user_register' => $row['zl_user_register']
    );
  }  

foreach (array_keys($row) as $column) {
if (!is_numeric($column)) {
$countQuery = "SELECT $column, COUNT(*) AS count FROM zl_zlecenia zl WHERE 1 ".$searchQuery." GROUP BY $column";
            $stmtCount = $db->prepare($countQuery);
            $stmtCount->execute($searchArray);
            $countResults = $stmtCount->fetchAll(PDO::FETCH_ASSOC);
            foreach ($countResults as $result) 
            {
                $searchPanesData["options"][$column][] = array(
                    "label" => $result[$column],
                    "total" => $totalRecords,
                    "value" => $result[$column],
                    "count" => $result['count']
                );
            }
        }
    }
$response = array(
    "draw" => intval($draw),
    "iTotalRecords" => $totalRecords,
    "iTotalDisplayRecords" => $totalRecordwithFilter,
    "aaData" => $data,
    "searchPanes" => $searchPanesData 
);
echo json_encode($response);

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

but if I use my data from ajax.php response and copy the array results to a json file and set serverSide to false, everything works fine. The data in datatable sorts and works with searchpanes.

When serverSide is enabled, the data should be returned from the server ready to be loaded - DataTables just loads that data as-is into the table. So the fix here would be to ensure that the PHP script is returning the data ready to go.

Colin

rwnukiewicz

I checked this all and PHP script returning the data ready.

echo json_encode($response);

{"draw":2,"iTotalRecords":5,"iTotalDisplayRecords":5,"aaData":[{"zl_zlecenia_numer":"211902","zl_nr_zam_dostawcy":"211902","id_kontrahenta":117311,"zl_user_register":"Marco Se"},{"zl_zlecenia_numer":"211903","zl_nr_zam_dostawcy":"211903","id_kontrahenta":117311,"zl_user_register":"Marco Se"},{"zl_zlecenia_numer":"211904","zl_nr_zam_dostawcy":"211904","id_kontrahenta":129115,"zl_user_register":"Adrian Mar"},{"zl_zlecenia_numer":"211905","zl_nr_zam_dostawcy":"211905","id_kontrahenta":111406,"zl_user_register":"Adrian Mar"},{"zl_zlecenia_numer":"211906","zl_nr_zam_dostawcy":"211906","id_kontrahenta":145932,"zl_user_register":"Seb Direct"}],"searchPanes":{"options":{"id_zlecenia":[{"label":17,"total":5,"value":17,"count":1},{"label":18,"total":5,"value":18,"count":1},{"label":19,"total":5,"value":19,"count":1},{"label":20,"total":5,"value":20,"count":1},{"label":21,"total":5,"value":21,"count":1}],"zl_zlecenia_numer":[{"label":"211902","total":5,"value":"211902","count":1},{"label":"211903","total":5,"value":"211903","count":1},{"label":"211904","total":5,"value":"211904","count":1},{"label":"211905","total":5,"value":"211905","count":1},{"label":"211906","total":5,"value":"211906","count":1}],"zl_nr_zam_dostawcy":[{"label":"211902","total":5,"value":"211902","count":1},{"label":"211903","total":5,"value":"211903","count":1},{"label":"211904","total":5,"value":"211904","count":1},{"label":"211905","total":5,"value":"211905","count":1},{"label":"211906","total":5,"value":"211906","count":1}],"id_kontrahenta":[{"label":117311,"total":5,"value":117311,"count":2},{"label":129115,"total":5,"value":129115,"count":1},{"label":111406,"total":5,"value":111406,"count":1},{"label":145932,"total":5,"value":145932,"count":1}],"zl_user_register":[{"label":"Marco Se","total":5,"value":"Marco Se","count":2},{"label":"Adrian Mar","total":5,"value":"Adrian Mar","count":2},{"label":"Seb Direct","total":5,"value":"Seb Direct","count":1}]}}}