从Datatables Custom Filter Not Working修改的代码

Question

I have a amateur sports club mamagement system that I have written using HTML5/PHP/Bootstrap, and use Datatables at various places.

For one of the tables, managing the waiting list, I need to filter a database set by a maximum and minimum age, so have used the code provided by Datatables in the following link (https://www.datatables.net/examples/plug-ins/range_filtering.html).

Everything about the table works, except changing the max and min values has no effect.

The head area has the following Javascript (copied from Datatables)

        $.fn.dataTable.ext.search.push(
                function (settings, data, dataIndex) {
                    var min = parseInt($('#min').val(), 10);
                    var max = parseInt($('#max').val(), 10);
                    var age = parseFloat(data[2]) || 0; // use data for the age column

                    if ((isNaN(min) && isNaN(max)) ||
                            (isNaN(min) && age <= max) ||
                            (min <= age && isNaN(max)) ||
                            (min <= age && age <= max))
                    {
                        return true;
                    }
                    return false;
                }
        );

The tables within the body section (html/php)

            <table border="0" cellspacing="5" cellpadding="5">
                <tr>
                    <td>Minimum age:</td>
                    <td><input type="number" id="min" name="min" min="3" max="21"></td>
                    <td>Maximum age:</td>
                    <td><input type="number" id="max" name="max" min="3" max="21"></td>
                </tr>
            </table>

            <table id = "table" class = "display compact cell-border stripe" width="100%">
                <thead>
                    <tr>
                        <th>Name</th>
                        <th>Gender</th>
                        <th>Age</th>
                        <th>Date Added</th>
                        <th>Action</th>
                    </tr>
                </thead>
                <tbody>
                    <?php
                    $query = $conn->query("SELECT * FROM `waitlist` WHERE wl_status ='Active'") or die(mysqli_error());
                    while ($f_query = $query->fetch_array()) {

                        echo '<tr>';
                        echo '<td>' . $f_query['wl_Name'] . '</td>';
                        echo '<td>' . $f_query['wl_Gender'] . '</td>';
                        echo '<td>' . getAge($f_query['wl_DoB']) . '</td>';
                        echo '<td data-sort="' . $f_query['wl_DateAdded'] . '">' . date('d/m/Y', strtotime($f_query['wl_DateAdded'])) . '</td>';
                        echo '<td><center><a href = "wl_edit.php?wl_id=' . $f_query['wl_ID'] . '" class = "btn btn-warning btn-sm"><span class = "fa fa-edit"></span> Details</a>' . ' | <a onclick = "javascript:confirmationDelete($(this)); return false;" href = "wl_delete.php?wl_id=' . $f_query['wl_ID'] . '" class = "btn btn-danger btn-sm"><span class = "fa fa-trash"></span> Delete</a>' . ' | <a  href = "wl_transfer.php?wl_id=' . $f_query['wl_ID'] . '" class = "btn btn-primary btn-sm"><span class = "fa fa-paste"></span> To Member</a></center></td>';
                        echo '</tr>';
                    };
                    ?>
                </tbody>
            </table>

The Javascript at bottom of page contains:

        $(document).ready(function () {
            $('#table').DataTable({
                "lengthChange": false,
                "pageLength": 12,
                "pagingType": "full_numbers",
                "order": [[3, "asc"]]
            });

            var table = $('#table').DataTable();
            $('#min, #max').keyup(function () {
                table.draw();
            });
        });

The keyup function part was copied from the Datatables link

The getage PHP function is part of the PHP header

function getAge($date) { // Y-m-d format
return intval(substr(date('Ymd') - date('Ymd', strtotime($date)), 0, -4));

I am still struggling with javascript, and expect that is where the problem lies, but cannot see the obvious cause of my problem. Thanks

Answer 1

The problem is with your getAge() function, and it should be obvious if you look at the age column in your table. Your code is:

function getAge($date) 
{ 
    return intval(substr(date('Ymd') - date('Ymd', strtotime($date)), 0, -4));
}

The PHP date() function returns a string in the format 'YYYMMDD'. You have two of them, and you subtract them. Suppose they are '20190414' and '20020321', subtracting them will result in 170093, taking the last two characters with substr() gives you an age of 93 for someone who was born in 2002. You didn't notice that?

So, let's write a better getAge() function. Working accurately with time is difficult. However, there's actually a function inside PHP to calculate date differences:

https://www.php.net/manual/en/datetime.diff.php

If we use that to create the function we get:

function getAge($dateStr) 
{ 
    $birthDate = new DateTime($dateStr); // check the valid formats in the manual!
    $currentDate = new DateTime();
    return $birthDate->diff($currentDate, true)->y; // return difference in years
}

This function could be written in one line of code, instead of three, but that would be harder to read, so don't do that.

Does this work for you? There could be other errors in your code, a good way to debug Javascript errors is to look at your developer tools.