Don't redraw the table in Datatables

Maxim_1

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

kthorngren

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

allan

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_1

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.

allan

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

Allan