Don't redraw the table in Datatables

Don't redraw the table in Datatables

Maxim_1Maxim_1 Posts: 15Questions: 5Answers: 1
edited November 2023 in Free community support

I try to make filters for my table, but when I want to draw only filtered data, it nothink do. I thin k that it happens because of after filter.php goes sort.php (https://i.stack.imgur.com/L0a3o.png).

var filterValues = {};
            var allTableData = [];
            var table = $('#logbook').DataTable({
                "processing": true,
                "serverSide": true,
                "pagingType": "full_numbers",
                "iDisplayLength": 20,

                renderer: '',
                "ajax": {
                    "url": "sort.php",
                    "type": "POST",
                },
                "language": {
                    "lengthMenu": "",

                },
                "columns": [
                    ///
                ]
            });

            function applyFilters() {
                var selectedFilters = {};
                $('.column-filter:checked').each(function () {
                    var columnIndex = $(this).data('column-index');
                    var filterValue = $(this).data('value');
                    if (!selectedFilters[columnIndex]) {
                        selectedFilters[columnIndex] = [];
                    }
                    selectedFilters[columnIndex].push(filterValue);
                });
                $.ajax({
                    url: 'filter.php',
                    method: 'POST',
                    data: {
                        filterParams: JSON.stringify(selectedFilters)
                    },
                    success: function (response) {

                        var filteredData = response;

                        table.clear();
                        table.rows.add(filteredData).draw();
                    },
                    error: function (error) {
                        console.error(error);
                    }
                });
            }

I tried to write ajax.reload() and almost everything, but nothing helps.

In Network filter look perfect

Edited by Kevin: 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

  • kthorngrenkthorngren Posts: 21,284Questions: 26Answers: 4,941
    Answer ✓

    You have server side processing enabled (serverSide: true). It is expected that the server script (sort.php) handle all sorting, searching and paging functions using the SSP protocol. To filter the columns use column().search() with draw() which will send an ajax request to the server with the search parameters.

    The clear() and rows.add() API's only work when using client side processing. Do you need server side processing enabled? See the processing modes doc.

    Possibly your above code will work if you disable server side processing. Or use column().search(). Another option is to use two tables with one being hidden. Display the above table and hide the other. When filtering hide the first table and display the second and use the applyFilters() to populate the second.

    Kevin

  • allanallan Posts: 63,419Questions: 1Answers: 10,456 Site admin

    Set ajax.dataSrc to be an empty string if you disable server-side processing (since you are just returning a top level array in the JSON).

    Allan

  • Maxim_1Maxim_1 Posts: 15Questions: 5Answers: 1
    edited November 2023

    I did that

               function applyFilters() {
                    var selectedFilters = {};
    
                    table.columns().search('').draw();
    
                    $('.column-filter:checked').each(function () {
                        var columnIndex = $(this).data('column-index');
                        var filterValue = $(this).data('value');
                        if (!selectedFilters[columnIndex]) {
                            selectedFilters[columnIndex] = [];
                        }
                        selectedFilters[columnIndex].push(filterValue);
                    });
    
                    for (var columnIndex in selectedFilters) {
                        if (selectedFilters.hasOwnProperty(columnIndex)) {
                            var filterValues = selectedFilters[columnIndex];
                            var filterRegex = '^' + filterValues.join('$|^') + '$';
                            table.columns(columnIndex).search(filterRegex, true, false);
                        }
                    }
    
                    table.draw();
                }
    

    ```php
    <?php
    $start = isset($_POST['start']) ? $_POST['start'] : 0;
    $length = isset($_POST['length']) ? $_POST['length'] : 20;
    $draw = isset($_POST['draw']) ? $_POST['draw'] : 1;
    $search = isset($_POST['search']['value']) ? $_POST['search']['value'] : '';

    $conn = new mysqli("localhost", "root", "HelloWorld2023!", "logbook");
    if ($conn->connect_error) {
    die("Ошибка: " . $conn->connect_error);
    }

    $columns = array(
    'id',
    'date',
    'partner',
    'category',
    'reason',
    'fromWho',
    'description',
    'dateToSend',
    'toWho',
    'howSend',
    'position'
    );

    $sql = "SELECT * FROM logbook WHERE 1 = 1";

    if (!empty($search)) {
    $sql .= " AND (";
    for ($i = 0; $i < count($columns); $i++) {
    if ($i > 0) {
    $sql .= " OR ";
    }
    $sql .= $columns[$i] . " LIKE '%" . $search . "%'";
    }
    $sql .= ")";
    }

    $selectedFilters = isset($_POST['filterParams']) ? json_decode($_POST['filterParams'], true) : [];

    if (!empty($selectedFilters)) {
    $sql .= " AND (";
    foreach ($selectedFilters as $columnIndex => $filterValues) {
    if (array_key_exists($columnIndex, $columns)) {
    $column = $columns[$columnIndex];
    $sql .= "(";
    foreach ($filterValues as $value) {
    $sql .= "$column = '$value' OR ";
    }
    $sql = rtrim($sql, " OR ");
    $sql .= ") AND ";
    }
    }
    $sql = rtrim($sql, " AND ");
    $sql .= ")";
    }

    $orderColumn = isset($_POST['order'][0]['column']) ? $_POST['order'][0]['column'] : 0;
    $orderDir = isset($_POST['order'][0]['dir']) ? $_POST['order'][0]['dir'] : 'asc';

    if (array_key_exists($orderColumn, $columns)) {
    $sql .= " ORDER BY " . $columns[$orderColumn] . " " . $orderDir;
    }

    if ($length != -1) {
    $sql .= " LIMIT $start, $length";
    }

    $result = $conn->query($sql);

    if ($result) {
    $data = array();
    while ($row = $result->fetch_assoc()) {
    $data[] = $row;
    }

    if ($length == -1) {
        $totalRecords = count($data);
        $filteredRecords = $totalRecords;
    } else {
        $totalRecords = $conn->query("SELECT COUNT(id) as total FROM logbook")->fetch_assoc()['total'];
        $filteredRecords = mysqli_num_rows($result);
    }
    
    $response = array(
        "draw" => intval($draw),
        "recordsTotal" => intval($totalRecords),
        "recordsFiltered" => intval($filteredRecords),
        "data" => $data,
    );
    
    echo json_encode($response);
    

    } else {
    echo "Ошибка при выполнении запроса: " . $conn->error;
    }

    $conn->close();

    <?php > ``` ?>

    And it still doesn't worl

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

  • allanallan Posts: 63,419Questions: 1Answers: 10,456 Site admin

    If you link to a test case showing the issue, I'd be happy to look at it in more detail.

    Allan

Sign In or Register to comment.