Updating initialised Datatable with data from ajax call

sunrunner4kr

Hi all,

I've been looking for a similar issue in the forums...but haven't found an answer that helps (or that I understood correctly).

I have Datatable which is already initialised:

$('#daytable').DataTable( {
                        "processing": true,
                        "serverSide": true,
                        "ajax": {
                            "url": "../scripts/getday.php",
                            "type": "POST",
                            "data": {
                                "day": dayName
                            }
                        },
                        "columns": [
                            { "data": "effort_id"},
                            { "data": "username" },
                            { "data": "resource_id" },
                            { "data": "no_hours" },
                            { "data": "effort_type" }
                        ]
                    } );

<table width="100%" class="table table-striped table-bordered table-hover display" id="daytable">
                                            <thead>
                                                <tr>
                                                    <th>Effort Id</th>
                                                    <th>User</th>
                                                    <th>Resource</th>
                                                    <th>Hours</th>
                                                    <th>Effort</th>
                                                </tr>
                                            </thead>
                                        </table>

from data that I receive from a php script from an ajax call.

This table is populated perfectly.

I then try and update this table if it is already initialised with another ajax call for fresh/different data:

if( $.fn.dataTable.isDataTable( '#daytable')) {
                    var table = $('#daytable').DataTable();
                    var table_data;

                    function doRefresh(data) {
                        table.clear();
                        console.log(data);
                        table.rows.add(data);
                        table.draw();
                    }

                    $.ajax( {
                        url: "../scripts/getday.php",
                        type: "POST",
                        data: {
                            day: dayName
                        },
                        success: function(data) {
                            doRefresh(data);
                        }
                    });

                }

My data is from the same script, with the same output of columns, for example:

[{"effort_id":"1","username":"simon.edge","resource_id":"1","no_hours":"2","effort_type":"Support"}]

However, I get the below error:

"table id=daytable - Requested unknown parameter 'effort_id' for row 0, column 0"

I suspect I'm not passing the data in the right format to the rows.add function, but can't see how I should be doing that?

Many thanks in advance,

Simon

colin

Hi Simon,

You probably need JSON.parse() - take a look at the example here.

Cheers,

Colin

sunrunner4kr

Thanks! I no longer get the error. But it's not updating my table data.

I've just commenting out the rows.add and can see that the table isn't even being cleared by the table.clear()

However by debugging, I can see it is hitting that line.

kthorngren

After table.clear() are you still executing table.draw()? This is needed for Datatables to display the updated (cleared) table.

Kevin

sunrunner4kr

@kthorngren Yes.

if( $.fn.dataTable.isDataTable( '#daytable')) {
                    var table = $('#daytable').DataTable();

                    $.ajax( {
                        url: "../scripts/getday.php",
                        type: "POST",
                        data: {
                            day: dayName
                        },
                        success: function(data) {
                            table.clear();
                            table.draw();
                            /*data = JSON.parse(data);
                            console.log(data);
                            table.rows.add(data.data).draw();*/
                        }
                    });

                }

colin

Odd, as it all looks OK, the code is pretty identical to what I sent. Would you be able to get a live example that we could look at?

C

sunrunner4kr

Finally got this online, so that you can have a look:

http://effort.banditbirds.co.uk/pages/effort.php

kthorngren

Interesting - the missing clue

You have this code:

                if( $.fn.dataTable.isDataTable( '#daytable')) {
                    var table = $('#daytable').DataTable();

                    $.ajax( {
                        url: "../scripts/getday.php",
                        type: "POST",
                        data: {
                            day: dayName
                        },
                        success: function(data) {
                            table.clear();
                            table.draw();
                            console.log(data);
                            /*data = JSON.parse(data);
                            console.log(data);
                            table.rows.add(data.data).draw();*/
                        }
                    });

                } else {
                    $('#daytable').DataTable( {
                        "processing": true,
                        "serverSide": true,
                        "ajax": {
                            "url": "../scripts/getday.php",
                            "type": "POST",
                            "data": {
                                "day": dayName
                            }
                        },
                        "columns": [
                            { "data": "effort_id"},
                            { "data": "username" },
                            { "data": "resource_id" },
                            { "data": "no_hours" },
                            { "data": "effort_type" }
                        ]
                    } );
                }

You have serverSide processing enabled. This doesn't seem to work well when you are performing your own ajax calls. Each time I click on a day two requests are sent to the server. This is seen in the Developer Tools > Network tab. The first is with your ajax call the second is resulting from having SSP enabled and is triggered by table.draw().

The first response:

{"draw":0,"recordsTotal":1,"recordsFiltered":1,"data":[{"effort_id":"2","username":"simon.edge","resource_id":"1","no_hours":"4","effort_type":"Support"}]}

The second response which has no data:

{"draw":20,"recordsTotal":0,"recordsFiltered":0,"data":[]}

I'm not sure about the table.clear() not working as I don't see data in the table on initial load so its always blank. I suspect that your rows.add() is working but then is cleared by the SSP request with no data in the response.

Do you need serverSide processing enabled? Try removing it to see if your rows.add()` works.

Kevin

sunrunner4kr

Bingo!

I removed this and i no longer get the 2nd ajax call.

I have to clear().draw() to get the table with no data.

Then add the data from the ajax call, and redraw.

if( $.fn.dataTable.isDataTable( '#daytable')) {
                    var table = $('#daytable').DataTable();

                    $.ajax( {
                        url: "../scripts/getday.php",
                        type: "POST",
                        data: {
                            day: dayName
                        },
                        success: function(data) {
                            table.clear().draw();
                            
                            console.log(data);
                            data = JSON.parse(data);
                            console.log(data);
                            table.rows.add(data.data).draw();
                        }
                    });

                }

Thanks for the help!!

kthorngren

Looks correct now.

Kevin