Update datatable with ajax request

Question

I want to implement a Datatable into a theme, which gets the data via an Ajax Request. Once the document is loaded, I build the HTML part for the datatable. The problem is: Once I click a sort function (for example sort one row ascending) it uses the original data to sort (which is given in the .php file) instead of the new JQuery loaded datatable. So probably I need to reinitialize the datatable or anything else?

HTML Part:

<tbody id="accountList">
    <!-- List all accounts -->
    <tr>
        <td>username@hostname-de</td>
        <td>PS</td>
        <td>350000</td>
        <td>45000</td>
        <td>Victor Ibarbo</td>
        <td>30 / 30</td>
        <td>224500</td>
        <td><label class="label label-success">Online</label></td>
    </tr>
</tbody>

JQuery part:

function buildAccountList(){
    $.ajax({
        url: "/database/accounts.php",
        type: "POST",
        data: {action: "selectAccounts"},
        success: function (response) {
            var opt = '';
            $.each(response, function(i, e){
                opt +='<tr>';
                opt += '<td>' + e.email + '</td>';
                opt += '<td>' + e.platform + '</td>';
                opt += '<td>' + e.coins + '</td>';
                opt += '<td>' + e.password + '</td>';
                opt += '<td>' + e.tradepileCards + '</td>';
                opt += '<td>' + e.tradepileValue + '</td>';
                opt += '<td>' + e.enabled + '</td>';
                opt += '</tr>';
            });
            $('#accountList').html(opt);
        },
        dataType: "json"
    });
}

The creation of the table works fine, but as I described, once I press a sort function it uses the old table (given by the html file). I hope you guys can help me.

irruputuncu · Accepted Answer · 2014-12-13T00:00:20.880

3

Are you using the jQuery DataTables plug-in? If so, they already have built-in functionality for ajax data sources: DataTables with AJAX

Alternatively, I think you should rather try to modify the table data itself in javascript instead of the rendered HTML. Using the DataTable API, especially table.clear(), table.rows.add() followed by a table.draw()(also check here), you should be able to update the data properly and use the order functionality afterwards.

In response to the comment: Basically something like this should be enough as an initialization of the datatable:

$(document).ready(function() {
    $('#example').dataTable( {
        "ajax": 'your url here'
    });
});

Then your json should be organized as:

{
  "data": [
    [
      'your columns',
      ...
    ],
  ]
}

If you want to not name the top-level key of your data 'data' you could set it by initializing with

"ajax": {
    "url": "data/objects_root_array.txt",
    "dataSrc": "your top-level key (or nothing for a flat array"
}

And as last option you can use objects instead of arrays in your ajax by adding a columns option to the initialization:

"ajax": ...,
"columns": [
    { "data": "email" },
    { "data": "platform" },
    { "data": "coins" },
    ...
]

and return json with objects like that:

{
    "email": "some@email.com",
    "platform": "PS",
    "coins": "320,800",
    ...
}

By the way, using this you would not even have to add a tbody to the table in the first place at it should be automatically be created by the plugin once it gets the AJAX data.

edited Dec 13 '14 at 00:00

answered Dec 12 '14 at 22:21

irruputuncu

1,460
1
11
24

I am using jQuery DataTables yes. And Ive read about this function, but I have no idea how I can do this. I mean I already return all json objects from my php file, but how will the Javascript/JQuery part look then for my case? – kentor Dec 12 '14 at 23:24
I've updated my response to include some examples to initialize your data table and format the corresponding json. There are even more in the [example section](http://www.datatables.net/examples/ajax/) of the DataTables website, all with the corresponding javascript, html and ajax response. – irruputuncu Dec 13 '14 at 00:11
Thank you for your work, I appreciate that. I tried your solution but I didn't succeed. I created a JS fiddle of my solution try (http://jsfiddle.net/c5jgrh7k/2/). By assigning the class "dataTable" for my html table tag it is "converted" to a JQuery dataTable by my theme. What is wrong with this try? :/. This is how my Ajax Request looks like: http://futpanel.com/database/accounts.php?action=selectAccounts . How can I specify the ajax type (POST/GET) and how can I assign the returned variables (for example email) to my table headers ? – kentor Dec 13 '14 at 17:21
Oh, I just saw, that you use a POST request to retrieve the data. According to [this example](https://datatables.net/examples/server_side/post.html) you can use the type option to set the request type to POST, but as you are also sending data, the simplest option for you is probably be to go back to your original approach: Doing the ajax request yourself and then update your data using the DataTables API. I created a [jsfiddle](http://jsfiddle.net/u9d95vq7/4/) that uses a mockup json api with your data and contains the column definitions and API calls to add the retrieved rows to your table. – irruputuncu Dec 15 '14 at 22:39
1

Better working jsfiddle link: [http://jsfiddle.net/u9d95vq7/7/](http://jsfiddle.net/u9d95vq7/7/) – irruputuncu Dec 15 '14 at 22:45

Update datatable with ajax request

1 Answers1

Linked