0

So I have seen that others have had this problem, but none of their solutions have helped so far. My goal is to print data retrieved from a database to a datatable. I am essentially following this code example: https://www.kodingmadesimple.com/2017/12/jquery-datatables-php-mysql-ajax.html. The problem is that the browser returns the error "DataTables warning: table id=tableName - Invalid JSON response. For more information about this error, please see http://datatables.net/tn/1". At this point, I am pretty stuck and not sure how to move forward. I have scoured the internet and have tried multiple solutions. Such as removing the "serverSide" option, ensuring the POD is enabled, added 'dataSrc': "", changed POST to GET (though I would prefer POST), etc. As you can see I am starting to just try anything which is a terrible approach which is why I am asking the community for help.

My index.html:

<body>
    <h2 style="text-align:center;">Wall of Sheep</h2>
    <table id="tableName" class="display" width="100%" cellspacing="0">
        <thead>
            <tr>
                <th>Username</th>
                <th>Unlocks</th>
                <th>Restriction</th>
                <th>Swap</th>
                <th>Total</th>
                <th>Last Offense</th>
            </tr>
        </thead>
    </table>

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/2.1.3/jquery.min.js" type="text/javascript"></script>
    <script src="//cdn.datatables.net/1.10.12/js/jquery.dataTables.js" charset="utf8" type="text/javascript"></script>

    <script type="text/javascript">
    $(document).ready(function() {
        $('#tableName').dataTable({
             "processing": true,
             "serverSide": true,
             "ajax": {
                "url": "fetch_data.php",
                "type": "POST",
                "dataSrc": ""
            },
            "columns": [
                {data: 'username'},
                {data: 'Unlocks'},
                {data: 'Restriction'},
                {data: 'Swap'},
                {data: 'Total'},
                {data: 'LastOffense'}
            ]
        });
    });
    </script>
</body>
</html>

My PHP (fetch_data.php) file:

<?php
// db settings
$hostname = 'localhost';
$username = 'root';
$password = 'password';
$database = 'DBname';

// db connection
$con = mysqli_connect($hostname, $username, $password, $database) or die("Error " . mysqli_error($con));

// fetch records
$sql = "SELECT username, Unlocks, Restriction, Swap, Total, LastOffense FROM Table1";
$result = mysqli_query($con, $sql);

while($row = mysqli_fetch_assoc($result)) {
    $array[] = $row;
}

$dataset = array(
    "echo" => 1,
    "totalrecords" => count($array),
    "totaldisplayrecords" => count($array),
    "data" => $array
);

echo json_encode($dataset);

?>

Returned preview snippet to include JSON data:

{"echo":1,"totalrecords":4401,"totaldisplayrecords":4401,"data":
[{
    "username": "123abc",
    "Unlocks": "0",
    "Restriction": "0",
    "Swap": "1",
    "Total": "1",
    "LastOffense": "2020-02-09 01:41:07"
}, {
    "username": "456def",
    "Unlocks": "0",
    "Restriction": "0",
    "Swap": "1",
    "Total": "1",
    "LastOffense": "2019-12-03 07:39:05"
}]}

I have followed the advice listed at https://datatables.net/manual/tech-notes/1. I still can't tell what the problem is. When I ran it through https://jsonlint.com/ it returned "Valid JSON" so I am not quite sure what is happening.

Also to add, I am sure some of the things I am doing is not considered best practice, this is my first time creating a website. I welcome all constructive criticism that can improve my code but please also try and help the problem at hand before critiquing other areas.

Noob
  • 23
  • 3
  • I am unable to reproduce your problem using the code you posted (with a dummy array instead of actually fetching from db, but with identical structure). The only difference I made is removing the `"dataSrc": ""` parameter, otherwise I'd just get "No matching records found". – El_Vanja Mar 27 '20 at 10:42
  • In case it is relevant: You are using server-side processing, so the server should be sending JSON which looks something like this: `{"draw": 1,"recordsTotal": 1234,"recordsFiltered": 123,"data": [...]}`. See the example in the Ajax tab [here](https://datatables.net/examples/server_side/post.html). Note the names being used, such as `recordsTotal` - not `totalrecords`, and so on. – andrewJames Mar 27 '20 at 12:36
  • [El_Vanja](https://stackoverflow.com/questions/60878506/datatables-warning-table-id-tablename-invalid-json-response#comment107718527_60878506) I have noticed that as well which why the problem is confusing me even more. – Noob Mar 27 '20 at 15:06
  • You have an error. Please read: [Should we ever check for mysqli_connect() errors manually?](https://stackoverflow.com/q/58808332/1839439) – Dharman Mar 27 '20 at 18:32
  • That is a great point, thank you [Dharman](https://stackoverflow.com/questions/60878506/datatables-warning-table-id-tablename-invalid-json-response#comment107732659_60878506)! – Noob Mar 27 '20 at 21:26

0 Answers0