Data table Server side Ajax Pagination

Question

I Have A location Table with nearly 14 thousand records, I need ajax pagination with server-side data. I used the Below Code But not working.

    <table class="table table-bordered table-striped table-hover dataTable js-exportable" id="htmltableID">
    <thead>
    <tr>

        <th>SNO</th>
        <th>Location</th>
        <th>City</th>
        <th>State</th>


    </tr>
    </thead>
    <tbody>

    <?php
    $i = 1;
    foreach ($data as $row) {
        echo "<tr>";
        echo "<td>" . $i . "</td>";
        echo "<td>" . $row->location . "</td>";
        echo "<td>" . $row->city . "</td>";
        echo "<td>" . $row->state . "</td>";
        echo "</tr>";
        $i++;
    }
    ?>
    </tbody>
</table>

<script>
    var oTable = "";
    $(document).ready(function () {
        oTable = $('#htmltableID').dataTable({
            "sPaginationType": "full_numbers",
            "bServerSide": true,
            "sAjaxSource": "location",
            "sServerMethod": "POST",
            "iDisplayLength": 5
        });
    });
</script>

By Using this Code I am getting error Msg "DataTables warning: table id=htmltableID - Cannot reinitialise DataTable. For more information about this error, please see http://datatables.net/tn/3" and "DataTables warning: table id=htmltableID - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1"

http://datatables.net/tn/1 It may be the issue with data which is returning from the ajax source url you provided location was not properly json encoded, Please check the response in browser console whether response is proper json or not — Vara Prasad
I think above code is running in same url location right?? — Vara Prasad
I have updated with json return data "location/loadRecord". now i dont know how to return the data with in the tbody tag. How to do this in above jquery — Brindha Baskaran

Vara Prasad Vara Prasad · Accepted Answer · 2018-11-13T11:35:54

You should have below type of code in main file. Let's say Maindata.php

<table class="table table-bordered table-striped table-hover dataTable js-exportable" id="htmltableID">
    <thead>
        <tr>
            <th>SNO</th>
            <th>Location</th>
            <th>City</th>
            <th>State</th>
        </tr>
    </thead>
</table>

<script>
    var oTable = "";
    $(document).ready(function () {
        oTable = $('#htmltableID').dataTable({
            "sPaginationType": "full_numbers",
            "bServerSide": true,
            "sAjaxSource": "location",
            "sServerMethod": "POST",
            "iDisplayLength": 5
        });
    });
</script>

And the data(json) needs to be fetch from other file, let's say loadrecords.php(In your case it was location/loadRecord)

<?php
// $data is the list of records fetched from database

// No need to print the data since we need to provide json data to dataTable, so Below code not required
/*
$i = 1;
foreach ($data as $row) {
    echo "<tr>";
    echo "<td>" . $i . "</td>";
    echo "<td>" . $row->location . "</td>";
    echo "<td>" . $row->city . "</td>";
    echo "<td>" . $row->state . "</td>";
    echo "</tr>";
    $i++;
}
*/

// Data which you needs to send in 'location/loadRecord' should be like this (In my case it was `loadrecords.php`)
/*{
    "data": [
        ["1","Location 1","City 1","State 1"],
        ["2","Location 2","City 2","State 2"],
        .....
        .....
        ["N","Location N","City N","State N"]
    ]
}*/

// Loop the data records fetched from database and prepare array in below format
$json_arr = array(
    "data" => array(
        array("1","Location 1","City 1","State 1"),
        array("2","Location 2","City 2","State 2"),
        ...............
        ...............
        array("N","Location N","City N","State N"),
    )
);
echo json_encode($json_arr);

data will have N no.of rows with 4 columns since you want to display 4 columns in the table so related data needs to be provided in json

Data table Server side Ajax Pagination

1 Answers