Jquery DataTable sorting not working when using ajax to populate data - Jquery ajax

MUHAMMADMESUM
December 20, 2021
252 views
0 votes
2 Answers

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:

Answers

- Sercan
- December 20, 2021 at 5:29 pm
- 0 votes
0
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%">
        
    </table>

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

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"
            }

        ]
    });

});

Please signup or login to give your own answer.

Click here to cancel reply.

Jquery DataTable sorting not working when using ajax to populate data – Jquery ajax

Answers

HTML

JavaScript