Datatable not loading from Ajax/php

chandrasoft

I searched, but couldn't find the solution. I am new to Datatable and I am using the latest 20 version. It is a simple dump of all rows from the table with few actions to be performed (CRUD). The data is JSON formatted from PHP, and the SON data is fine (validated with jsoninit), but datable is NOT displaying any row. It is stuck in processing. Please help. Here is my table definition. Please let me know what other info would you need to resolve this issue ? Appreciate your time and help.

var eventData = $('#eventTable').DataTable({
        "processing":true,      

        "ajax":{
            url: 'action.php',
            type: 'POST',
            data: {action:'listEvent'},
            dataType: 'json'
            },

        "columns": [
                    { "data": "eventid" },
                    { "data": "eventdate" },
                    { "data": "eventname" },                        
                    { "data": "totsongs" },
                    { "data": "action" },
                    { "data": "eventsongs"}
            ],

        "columnDefs":[ {
            "targets": "_all",
            "orderable": false,
            "searchable": false
            }],

        "lengthMenu": [[10, 25, 50, 100, -1], [10, 25, 50, 100, "All"]],

        "Language": {
            "Paginate": {
                "First":       " ",
                "Previous":    " ",
                "Next":        " ",
                "Last":        " ",
            },

          "LengthMenu":    "Records per page: _MENU_",
          "Info":          "Total of _TOTAL_ records (showing _START_ to _END_)",
          "InfoFiltered":  "(filtered from _MAX_ total records)"
        }
    }); 

Edited by Colin - Syntax highlighting. Details on how to highlight code using markdown can be found in this guide.

colin

Are there any console errors? And, can you post some of the JSON records (say the first 30 lines or so) and the HTML table definition, please - normally these errors are cause due to a mismatch between the JSON, the HTML and the table column declarations.

Colin

chandrasoft

Hello Colin, thanks for the reply. I have given below, partial JSON data, HTML code and PHP code.

JSON data (partial), which is validated by jsoninit.

[{"eventid":"1","eventdate":"2020-04-02","eventname":"FIRST EVENTu","totsongs":5,"action":"<button type=\"button\" name=\"update\" id=\"1\" class=\"btn btn-warning btn-xs update\"><span class=\"glyphicon glyphicon-pencil\" data-toggle=\"tooltip\" title=\"Edit this Event\"><\/span><\/button> <button type=\"button\" name=\"copy\" id=\"1\" class=\"btn btn-success btn-xs copy\"><span class=\"glyphicon glyphicon-file\" data-toggle=\"tooltip\" data-original-title=\"Copy this Event\"><\/span><\/button> <button type=\"button\" name=\"song\" id=\"1\" class=\"btn btn-primary btn-xs song\"><span data-toggle=\"tooltip\" data-original-title=\"Add or Update songs for this event\">Select Songs<\/span><\/button> <button type=\"button\" name=\"excel\" id=\"1\" class=\"btn btn-info btn-xs excel\"><span data-toggle=\"tooltip\" data-original-title=\"Download Song list for this event\">Download Song list<\/span><\/button> <button type=\"button\" name=\"delete\" id=\"1\" class=\"btn btn-danger btn-xs delete\"><span class=\"glyphicon glyphicon-trash\" data-toggle=\"tooltip\" data-original-title=\"Delete this Event\"><\/span><\/button> ","eventsongs":"TP000001,TP000002,TP000003,TP000004,TP000005"},{"eventid":"2","eventdate":"2020-04-04","eventname":"April 4th event","totsongs":1,"action":"<button type=\"button\" name=\"update\" id=\"2\" class=\"btn btn-warning btn-xs update\"><span class=\"glyphicon glyphicon-pencil\" data-toggle=\"tooltip\" title=\"Edit this Event\"><\/span><\/button> <button type=\"button\" name=\"copy\" id=\"2\" class=\"btn btn-success btn-xs copy\"><span class=\"glyphicon glyphicon-file\" data-toggle=\"tooltip\" data-original-title=\"Copy this Event\"><\/span><\/button> <button type=\"button\" name=\"song\" id=\"2\" class=\"btn btn-primary btn-xs song\"><span data-toggle=\"tooltip\" data-original-title=\"Add or Update songs for this event\">Select Songs<\/span><\/button> <button type=\"button\" name=\"excel\" id=\"2\" class=\"btn btn-info btn-xs excel\"><span data-toggle=\"tooltip\" data-original-title=\"Download Song list for this event\">Download Song list<\/span><\/button> <button type=\"button\" name=\"delete\" id=\"2\" class=\"btn btn-danger btn-xs delete\"><span class=\"glyphicon glyphicon-trash\" data-toggle=\"tooltip\" data-original-title=\"Delete this Event\"><\/span><\/button> ","eventsongs":"TP000001"},

and here is the HTML that defines the eventTable:

<div class="col-lg-10 col-md-10 col-sm-9 col-xs-12">        
    <div class="panel-heading">
        <div class="row">
            <div class="col-md-10">
                <h3 class="panel-title"></h3>
            </div>
            <div class="col-md-2" align="right">
                <button type="button" name="addEvent" id="addEvent" class="btn btn-success btn-sm"><span data-toggle="tooltip" data-original-title="Add New Event"><i class="glyphicon glyphicon-plus"></i>&nbsp;&nbsp;ADD NEW EVENT</span></button>
            </div>
        </div>
    </div>
    <table id="eventTable" class="table table-bordered table-striped tptable_style">
        <thead>
            <tr>
                <th>Event ID</th>
                <th>Event Date</th>
                <th>Event Name</th>
                <th>Total Songs</th>
                <th>Action</th> 
                <th></th>   
            </tr>
        </thead>
    </table>
</div>

and here is the PHP code that gets the data from table:

    $eventData = array();   
    $sqlQuery = "SELECT eventid, eventdate, eventname, eventsongs FROM ".$this->eventTable." ";
    $result = mysqli_query($this->dbConnect, $sqlQuery);

    while( $event = mysqli_fetch_assoc($result) ) {     

        $eventRows = array();   
        // count total songs from eventsongs field - comma separated songlist
        $totsongs = 0; 
        if (trim($event['eventsongs']) !== '') {
            $totsongs = substr_count($event['eventsongs'], ",") + 1;
        }

        // assign row buttons
        $upbtn = '<button type="button" name="update" id="'.$event["eventid"].'"  class="btn btn-warning btn-xs update"><span class="glyphicon glyphicon-pencil" data-toggle="tooltip" title="Edit this Event"></span></button>  ';


        $delbtn = '<button type="button" name="delete" id="'.$event["eventid"].'"  class="btn btn-danger btn-xs delete"><span class="glyphicon glyphicon-trash" data-toggle="tooltip" data-original-title="Delete this Event"></span></button>  ';

        $cpybtn = '<button type="button" name="copy" id="'.$event["eventid"].'"  class="btn btn-success btn-xs copy"><span class="glyphicon glyphicon-file" data-toggle="tooltip" data-original-title="Copy this Event"></span></button>  ';

        $songbtn = '<button type="button" name="song" id="'.$event["eventid"].'"  class="btn btn-primary btn-xs song"><span data-toggle="tooltip" data-original-title="Add or Update songs for this event">Select Songs</span></button>  ';

        if ($totsongs > 0) {
            $excelbtn = '<button type="button" name="excel" id="'.$event["eventid"].'"  class="btn btn-info btn-xs excel"><span data-toggle="tooltip" data-original-title="Download Song list for this event">Download Song list</span></button>  ';
        } else {
            $excelbtn = '<button type="button" disabled name="excel" id="'.$event["eventid"].'"  class="btn btn-light btn-xs excel"><span data-toggle="tooltip" data-original-title="Download Song list for this event">Download Song list</span></button>  ';              
        }

        $eventid   = $event['eventid']; 
        $eventdate = $event['eventdate'];   
        $eventname = $event['eventname'];   
        $totsongs  = $totsongs;                             
        $action    = $upbtn . "  " . $cpybtn . "  " . $songbtn . "  " . $excelbtn . "  " . $delbtn;
        $eventsongs = $event['eventsongs']; // this is a hidden field list of songs

        $eventData[] = array("eventid" => $eventid,
                             "eventdate" => $eventdate,
                             "eventname" => $eventname,
                             "totsongs" => $totsongs,
                             "action" => $action,
                             "eventsongs" => $eventsongs);

    }

$dbg = print_r(json_encode($eventData), true);
file_put_contents('jsondata.txt', $dbg);        

    echo json_encode($eventData);

}

chandrasoft

Forgot to mention. There is no Console Errors.

colin

Because the data is at the top-level of the returned data, you need to add "" for ajax.dataSrc, see the middle example on that page.

Colin

chandrasoft

I added ajax.dataSrc = "", but issue is still the same. But this time I got console error, pasted below:

[Warning] jQuery.Deferred exception: undefined is not an object (evaluating 'n[q].style') (2) (jquery.min.js, line 2)
Ja — datatables.min.js:88:169
ja — datatables.min.js:74
e — datatables.min.js:118:115
(anonymous function) — datatables.min.js:118:200
each — jquery.min.js:2:2578
q — datatables.min.js:108:393
(anonymous function) — datatables.min.js:191:498
(anonymous function) — data.js:6
l — jquery.min.js:2:29381
(anonymous function) — jquery.min.js:2:29679
undefined
[Error] TypeError: undefined is not an object (evaluating 'n[q].style')
(anonymous function) (jquery.min.js:2:31048)

colin

I think we're going to need to see this. Could you link to your page, please, so we can take a look.

Colin

chandrasoft

Here is the link to my page.

kthorngren

You have 6 columns defined in Datatables but technically you have only 5 defined in HTML. Here is your HTML:

        <table id="eventTable" class="table table-bordered table-striped tptable_style">
            <thead>
                <tr>
                    <th>Event ID</th>
                    <th>Event Date</th>
                    <th>Event Name</th>
                    <th>Total Songs</th>
                    <th>Action</th> 
                    </th></th>
                </tr>
            </thead>
        </table>

You have </th></th> for the 6th column but it should be <th></th>.

Also you have some duplicate id errors that are outside of Datatables but you might want to clean those up.

Kevin

chandrasoft

I corrected <th> tags but it didn't solve the issue

kthorngren

Did you update the test case? It still looks the same to me.

Kevin

chandrasoft

I take back the above stmt. Silly me,correctring <th> tag resolved the issue, after clearing the browser cache. Thanks a lot Kthorngern and Colin, for the wonderful support and help. Appreciate the great prodcut.