Search API by Server-Side filters not the table

axhogbue

Link to test case: I can not give, because my project runs under XAMPP on my local machine
Debugger code (debug.datatables.net):

my data.admin.user.js

$(document).ready(function() {

    var userdataTable = $('#userList').DataTable(
        {
            lengthMenu: [
                [25, 50, 100],
                [25, 50, 100],
            ],
            lengthChange: false,
            pageLength: 50,
            processing: true,
            serverSide: true,
            searching: true,
            search: {
                return: true,
            },
            paging: true,
            info: false,
            dom: 'lrtip',          
            language: {
                lengthMenu: 'Zeige _MENU_ Einträge pro Seite',
                zeroRecords: 'Nichts gefunden - Tut mir leid',
                info: 'Zeige Seite _PAGE_ von _PAGES_',
                infoEmpty: 'Keine Daten verfügbar',
                infoFiltered: '(gefiltert von _MAX_ Einträgen)',
                search: 'Suche: ',
                previous: 'vorher',
                next: 'nächster',
            },
            ajax: {
                url: "action.php",
                type: "POST",
                data: { action: 'userList'},
                dataType: "json"
            }
        }
    );

    $('#userSearch').on('keyup', function() {
        userdataTable.search($(this).val()).draw();
    });
} );

action.php

if(!empty($_POST['action']) && $_POST['action'] == 'userList'){
    $phoenix->getUserList();
}

phoenix.php

    public function getUserList(){
        $sqlUnfiltered = "SELECT usr.*, grp.Name AS Gruppenname, grp.Is_Admin FROM tbl_user AS usr
                LEFT JOIN tbl_usergroups AS usrgrp ON usr.ID = usrgrp.UserID
                LEFT JOIN tbl_groups AS grp ON usrgrp.GroupID = grp.ID ";

        $sql = $sqlUnfiltered;  

        // Suchkriterium
        if(!empty($_POST['search']['value'])){
            $sql .= "WHERE";
            $sql .= " (usr.id LIKE '%" . $_POST['search']['value'] . "%')";
            $sql .= " OR (usr.name LIKE '%" . $_POST['search']['value'] . "%')";
            $sql .= " OR (grp.name LIKE '%" . $_POST['search']['value'] . "%')";
        }

        // Sortierung
        if(!empty($_POST["order"])){
            $sql .= " ORDER BY " . $_POST["order"][0]["column"] + 1 . " " . $_POST["order"][0]["dir"] . " ";
        } else {
            $sql .= " ORDER BY id ASC";
        }

        // Limitierungen
        if (!empty($_POST["length"])){
            $sql .= " LIMIT " . $_POST["start"] . ", " . $_POST["length"];
        }

        $result = mysqli_query($this->dbConnect, $sql);
        $resultUnfiltered = mysqli_query($this->dbConnect, $sqlUnfiltered);
        $rowsCountFiltered = mysqli_num_rows($result);
        $rowsCountUnfiltered = mysqli_num_rows($resultUnfiltered);
        $userData = array();
        
        while( $user = mysqli_fetch_assoc($result)){

            if($user['Aktiv'] == 'Y'){
                $AktivHTML = '<label class = "form-check form-switch"><input class="form-check-input" type="checkbox" checked disabled></input></label>';
            } else {
                $AktivHTML = '<label class = "form-check form-switch"><input class="form-check-input" type="checkbox" disabled></input></label>';
            }

            if($user['Is_Admin'] == 'Y'){
                $IsAdminHTML = '<label class = "form-check form-switch"><input class="form-check-input" type="checkbox" checked disabled></input></label>';
            } else {
                $IsAdminHTML = '<label class = "form-check form-switch"><input class="form-check-input" type="checkbox" disabled></input></label>';
            }
            
            $aktionen = '<div class="btn-list flex-nowrap">';
            $aktionen .= '<a href="#" class="btn">Bearbeiten</a>';
            $aktionen .= '<a href="#" class="btn">L&ouml;schen</a>';
            $aktionen .= '</div>';

            $userRows = array();
            $userRows[] = $user['ID'];
            $userRows[] = $user['Name'];
            $userRows[] = $user['EMail'];
            $userRows[] = $AktivHTML;
            $userRows[] = $user['Gruppenname'];
            $userRows[] = $IsAdminHTML;
            $userRows[] = $aktionen;
            $userData[] = $userRows;
        }

        $output = array(
            "draw"              =>  1,
            "recordsTotal"      =>  $rowsCountUnfiltered,
            "recordsFiltered"   =>  $rowsCountFiltered,         
            "data"              =>  $userData
        );

        echo json_encode($output);
        
    }

Error messages shown:

No error messages

Description of problem:

My problem is that the datatable do not filter the result after I enter a search key. With the developertools in Chrome I see that my search key works. In the response you get only 1 item back from 3 items in total, but in my datatable you will see all items.

I try to get a solution for 3 days and I have no idea where I have the failure.

Please can you help me?

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

colin

It looks like you've hard-coded draw to be 1 in your response. That value should match the value that's in the request so that DataTables can equate the response to the request.

For more information, the protocol is discussed here. Also see examples here.

Colin

axhogbue

Why didn't I see this error!!! Many thanks. In my first release I get the error of missing $_POST['draw'] and I think that is the reason why I have it hard-coded.

Now it works.