Search API by Server-Side filters not the table

Search API by Server-Side filters not the table

axhogbueaxhogbue Posts: 2Questions: 1Answers: 0
edited April 2023 in Free community support

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.

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 15,177Questions: 1Answers: 2,590
    Answer ✓

    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

  • axhogbueaxhogbue Posts: 2Questions: 1Answers: 0

    :( :( :( :( :( :( :( :( :(

    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.

Sign In or Register to comment.