Jquery DataTable sorting not working when using ajax to populate data

Question

I have been trying to use ajax to populate data in a table on page load. The data is loaded fine in my table but The issue I'm facing is with sorting and pagination of jQuery. as when ever i click its sorting arrow, it shows no data available in the table. My table code is:

{{-- /Table Starts form here --}}
<table id="DtAdminAttendance" class="table table-striped custom-table mb-0 datatable">
    <thead>
        <tr>
            {{-- <th style="display: none">tbl_index</th> --}}
            <th>Emp ID - Name</th>
            <th>Date </th>
            <th>Punch In</th>
            <th>Punch Out</th>
            <th>Worked Hours</th>
            <th>Status</th>
            <th class="text-right">Action</th>
        </tr>
    </thead>
    
    <tbody id="atn-tbody">
        {{-- table data goes here --}}
    </tbody>
</table>

My ajax for this table is:

<script>
    //for displaying table data department
    $(document).ready(function () {
        // var table = $('#DtAdminAttendance').DataTable();
        $.ajax({
            type: "GET",
            url: "fetch-Attendance",
            dataType: "json",
            success: function (response) {
                $('tbody').html("");
                $.each(response.Attendance_list, function (key, employee) {
                    if (employee.status == "Absent") 
                    {
                        $('tbody').append(
                            `<tr>\
                                <td style="display: none"> ${employee.id} </td>\
                                <td> ${employee.employeeID} - ${employee.name} </td>\
                                <td> ${employee.date} </td>\
                                <td> ${employee.Punch_in} </td>\
                                <td> ${(employee.Punch_Out == null ? '-' : employee.Punch_Out)} </td>\
                                <td> ${employee.totalhours} </td>\
                                <td class="badge  badge-danger"> ${employee.status} </td>\

                                <td class="text-right">
                                    <div class="dropdown dropdown-action">
                                        <a href="#" class="action-icon dropdown-toggle" data-toggle="dropdown" aria-expanded="false">
                                            <i class="material-icons">more_vert</i>
                                        </a>

                                        <div class="dropdown-menu dropdown-menu-right">
                                            <button type="button" class="dropdown-item edtAtn" href="#" data-toggle="modal" data-target="#edit_Attendance" value="  ${employee.id}">
                                                <i class="fa fa-pencil m-r-5"></i> Edit
                                            </button>

                                            <button type="button" class="dropdown-item dltAtn" href="#" data-toggle="modal" data-target="#delete_Attendance" value="${employee.id}">
                                                <i class="fa fa-trash-o m-r-5"></i> Delete
                                            </button>
                                        </div>
                                    </div>\
                                </td>\
                            </tr>`);
                    }
                    else if (employee.status == "Present") 
                    {
                        $('tbody').append(
                            `<tr>\
                                <td style="display: none"> ${employee.id} </td>\
                                <td> ${employee.employeeID} '-' {employee.name}</td>\
                                <td> ${employee.date}</td>\
                                <td> ${employee.Punch_in}</td>\
                                <td> ${(employee.Punch_Out == null ? '-' : employee.Punch_Out)}</td>\
                                <td> ${employee.totalhours}</td>\
                                <td class="badge  badge-success"> ${employee.status}</td>

                                <td class="text-right">
                                    <div class="dropdown dropdown-action">
                                        <a href="#" class="action-icon dropdown-toggle" data-toggle="dropdown" aria-expanded="false">
                                            <i class="material-icons">more_vert</i>
                                        </a>

                                        <div class="dropdown-menu dropdown-menu-right">
                                            <button type="button" class="dropdown-item edtAtn" href="#" data-toggle="modal" data-target="#edit_Attendance" value="${employee.id}" >
                                                <i class="fa fa-pencil m-r-5"></i> Edit
                                            </button>

                                            <button type="button" class="dropdown-item dltAtn" href="#" data-toggle="modal" data-target="#delete_Attendance" value="${employee.id}">
                                                <i class="fa fa-trash-o m-r-5"></i> Delete
                                            </button>
                                        </div>
                                    </div>\
                                </td>\
                            </tr>`);
                    }
                    else if (employee.status == "Late") 
                    {
                        $('tbody').append(
                            `<tr>\
                                <td style="display: none"> ${employee.id} </td>\
                                <td> ${employee.employeeID} '-' ${employee.name} </td>\
                                <td> ${employee.date}</td>\
                                <td> ${employee.Punch_in}</td>\
                                <td> ${(employee.Punch_Out == null ? '-' : employee.Punch_Out)}</td>\
                                <td> ${employee.totalhours}</td>\
                                <td class="badge  badge-warning"> ${employee.status} </td>

                                <td class="text-right">
                                    <div class="dropdown dropdown-action">
                                        <a href="#" class="action-icon dropdown-toggle" data-toggle="dropdown" aria-expanded="false">
                                            <i class="material-icons">more_vert</i>
                                        </a>

                                        <div class="dropdown-menu dropdown-menu-right">
                                            <button type="button" class="dropdown-item edtAtn" href="#" data-toggle="modal" data-target="#edit_Attendance" value="${employee.id}">
                                                <i class="fa fa-pencil m-r-5"></i> Edit
                                            </button>

                                            <button type="button" class="dropdown-item dltAtn" href="#" data-toggle="modal" data-target="#delete_Attendance" value="${employee.id}">
                                                <i class="fa fa-trash-o m-r-5"></i> Delete
                                            </button>
                                        </div>
                                    </div>\
                                </td>\
                            </tr>`);
                    } 
                    else if (employee.status == "Unpaid Halfday") 
                    {
                        $('tbody').append(
                            `<tr>\
                                <td style="display: none"> ${employee.id} </td>\
                                <td> ${employee.employeeID} '-' ${employee.name} </td>\
                                <td> ${employee.date}</td>\
                                <td> ${employee.Punch_in}</td>\
                                <td> ${(employee.Punch_Out == null ? '-' : employee.Punch_Out)} </td>\
                                <td> ${employee.totalhours}</td>\
                                <td class="badge  badge-info"> ${employee.status} </td>

                                <td class="text-right">
                                    <div class="dropdown dropdown-action">
                                        <a href="#" class="action-icon dropdown-toggle" data-toggle="dropdown" aria-expanded="false">
                                            <i class="material-icons">more_vert</i>
                                        </a>

                                        <div class="dropdown-menu dropdown-menu-right">

                                        <button type="button" class="dropdown-item edtAtn" href="#" data-toggle="modal" data-target="#edit_Attendance" value="${employee.id}">
                                            <i class="fa fa-pencil m-r-5"></i> Edit
                                        </button>

                                        <button type="button" class="dropdown-item dltAtn" href="#" data-toggle="modal" data-target="#delete_Attendance" value="${employee.id}" >
                                            <i class="fa fa-trash-o m-r-5"></i> Delete
                                        </button>
                                    </div></div>
                                </td>
                            </tr>`);
                    }
                });
                $('#DtAdminAttendance').DataTable();
            }
        });
    });
</script>

Now when I go to the page it loads the table rows:

But when I sort using any of the column it shows me no data available in table:

What have you tried to resolve the problem? Is this a PHP problem, where the data is not sent to the browser in the expected format, or a JS problem, where the browser does not handle that data properly? — Nico Haase
– Nico Haase, Commented Dec 21, 2021 at 6:55

steven7mwesigwa · Accepted Answer · 2021-12-21 02:22:25Z

Based on my tests, your issue isn't reproducible. It works fine on my side.

JS Fiddle: OP's test code

Nonetheless, your source code could use some improvement and probably this may resolve your issue.

Instead of manually manipulating the HTML table, use the dataTable API to automatically generate the table.

JS Fiddle: Improved source code

HTML

<table id="DtAdminAttendance" class="table table-striped custom-table mb-0 datatable">
    <thead>
        <tr>
            <th>tbl_index</th>
            <th>Emp ID - Name</th>
            <th>Date</th>
            <th>Punch In</th>
            <th>Punch Out</th>
            <th>Worked Hours</th>
            <th>Status</th>
            <th class="text-right">Action</th>
        </tr>
    </thead>
    <tbody id="atn-tbody">
    </tbody>
</table>

JavaScript

Change the ajax: {url: '...'} according to your needs. I.e: '/fetch-Attendance'.

$(document).ready(function () {
    $("#DtAdminAttendance").DataTable({
        scrollX: true,
        autoWidth: false,
        ajax: {
            type: "GET",
            url: "https://run.mocky.io/v3/9ef15223-a192-4f3c-a2fb-843048cb31dc",
            dataType: "JSON",
            dataSrc: "Attendance_list",
            async: true,
        },
        columns: [
            {data: "id"},
            {
                data: "name", "fnCreatedCell": function (nTd, sData, oData, iRow, iCol) {
                    $(nTd).html(`${oData.employeeID + "-" + oData.name}`);
                }
            },
            {data: "date"},
            {data: "Punch_in"},
            {data: "Punch_Out"},
            {data: "totalhours"},
            {
                data: "status", "fnCreatedCell": function (nTd, sData, oData, iRow, iCol) {
                    const statusBtn = $("<span class='badge'></span>");
                    let badgeColor = "badge-secondary";

                    switch (oData.status) {
                        case "Absent":
                            badgeColor = "badge-danger";
                            break;
                        case "Present":
                            badgeColor = "badge-success";
                            break;
                        case "Late":
                            badgeColor = "badge-warning";
                            break;
                        case "Unpaid Halfday":
                            badgeColor = "badge-info";
                            break;

                    }

                    statusBtn.addClass(badgeColor);
                    statusBtn.text(oData.status);

                    $(nTd).html(statusBtn.get(0));
                }
            },
            {
                data: null, "fnCreatedCell": function (nTd, sData, oData, iRow, iCol) {
                    const actionBtn = $(`
                    <div class="d-inline-block text-right">
                        <div class="dropdown dropdown-action">
                            <a href="#" class="action-icon dropdown-toggle" data-toggle="dropdown" aria-expanded="false">
                                <i class="material-icons">more_vert</i>
                            </a>
                            <div class="dropdown-menu dropdown-menu-right">
                                <button style="cursor: pointer;" type="button" class="dropdown-item edtAtn" data-toggle="modal" data-target="#edit_Attendance" value="${oData.id}">
                                    <i class="fa fa-pencil m-r-5"></i>
                                    Edit
                                </button>
                                <button style="cursor: pointer;" type="button" class="dropdown-item dltAtn" data-toggle="modal" data-target="#delete_Attendance" value="${oData.id}">
                                    <i class="fa fa-trash-o m-r-5"></i>
                                    Delete
                                </button>
                            </div>
                        </div>
                    </div>
                `);

                    $(nTd).html(actionBtn.get(0));
                },
                orderable: false,
                searchable: false
            }
        ],
        columnDefs: [
            {
                "targets": [0],
                "visible": false
            },
            {
                "defaultContent": "-",
                "targets": "_all"
            }

        ]
    });

});

Sercan · Accepted Answer · 2021-12-20 16:51:27Z

1

After adding the jQuery (script) and dataTable (script, style) references to the HTML file, you should add the following code to the script to sort the dataTable. For the project to work properly, you should add the script you will run to the last file. As far as I've commented, there's no problem applying the dataTable style. You probably didn't add the dataTable script reference to the project. Retest the project using the references in the template below and the commands in the <script> element.

<!DOCTYPE html>
<html lang="en">
    
<head>
    <meta charset="UTF-8">
    <title>Document</title>

    <link rel="stylesheet" href="https://cdn.datatables.net/1.11.3/css/jquery.dataTables.min.css">
    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://cdn.datatables.net/1.11.3/js/jquery.dataTables.min.js"></script>
</head>

<body>
    <table id="DtAdminAttendance" class="display" style="width:100%">
        <!-- Something -->
    </table>

    <script>
        $(document).ready(function () {
            $('#DtAdminAttendance').DataTable({
                order: [
                    [3, 'desc'],
                    [0, 'asc']
                ]
            });
        });
    </script>
</body>
</html>

edited Dec 20, 2021 at 16:51

answered Dec 20, 2021 at 16:29

Sercan

5,1433 gold badges23 silver badges42 bronze badges

8 Comments

MUHAMMAD MESUM Over a year ago

It would be a great help if you can explain a little bit more, i am using laravel and all my js and css files are getting load from there. this one is my custom script. now when i have added this code in my script it is giving me error **DataTables warning: table id=DtAdminAttendance - Cannot reinitialise DataTable. For more information about this error, please see datatables.net/tn/3 **

MUHAMMAD MESUM Over a year ago

i have included it as follow code $(document).ready(function () { $('#DtAdminAttendance').DataTable({ order: [ [3, 'desc'], [0, 'asc'] ] }); my ajax is below this one

Sercan Over a year ago

I added that line to show it. Reference is here.

Sercan Over a year ago

I don't know Laravel and php structure. If rendering as in ASP.NET, you may need to specify the scripts I specified in a field like @section scripts{}. If you have root layout, add direct script references there. When this page is rendered, your scripts will already be loaded on the page.

Sercan Over a year ago

Or if the data is coming to the page dynamically, the problem may be in the backend, not the frontend.

|

Collectives™ on Stack Overflow

Jquery DataTable sorting not working when using ajax to populate data

2 Answers 2

HTML

JavaScript

Comments

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

HTML

JavaScript

Comments

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related