One AJAX call for all tables, but getting error 4

jonmrich

I'm trying to use this to get data for about 8 tables I have on a long page.

It gets the data just fine if I console.log. However, when I try to render the table, I get this error:

Requested unknown parameter '1' for row 0. For more information about this error, please see http://datatables.net/tn/4

$.ajax({
url: 'all_data.php',
type: 'GET',
success: function(data) {
$("#recode_num").DataTable({
      data:data,
      "columns": [
            { "title": "cat_code" },
            { "title": "value" },
            { "title": "database_percent" },
            { "title": "national_percent" },
            { "title": "index_value" }
          ],

      });
  }
});

However, if I do this, it works fine, so I know I have the right number of columns, cells, etc.

 $("#recode_num").DataTable({
     "ajax": {
      "url":"all_data.php",
      "type":"GET"
      } ,
      "columns": [
            { "title": "cat_code" },
            { "title": "value" },
            { "title": "database_percent" },
            { "title": "national_percent" },
            { "title": "index_value" }
          ],

      });

What else could be causing this problem?

allan

Can you link to the page, as per the forum rules please. The above looks fine, so it must be something else.

Allan

jonmrich

Sorry Allan...Had to come up with a page just for this:

REMOVED

ignignokt

Wouldn't it be data:data.data instead?

jonmrich

@ignignokt

Afraid that didn't help. No error, but also "No data available in table" shows in the table

allan

Hi,

Super - thanks for the link, and for picking up the DataTables support option!

There are a few points to make:

1) I think the biggest one is about the markup used for the table - it looks very close, but you have 5 empty tr rows in your thead and no columns! I would suggest changing the HTML to be:

<table class="table table-bordered" id="main_index" style="height:150px">
    <thead>
        <tr>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
            <th></th>
        </tr>
    </thead>
</table>

i.e. define 5 columns. If you are going to do it that way then it would also be worth writing in the column titles in the HTML rather than using the columns.title option.

Alternatively use:

<table class="table table-bordered" id="main_index" style="height:150px"></table>

and let DataTables create the markup for you if you want to use columns.title.

2) @ignignokt is correct for your example, but you need to add dataType:"json" to your Ajax options. However...

3) Rather than making the Ajax request yourself, let DataTables make it for you using the ajax option:

$("#main_index").DataTable( {
  ajax: 'all_data.php'
} );

4) The JSON parameters draw, recordsTotal and recordsFiltered are only needed if you are using server-side processing. Given the number of records you have you might want to consider that, but it isn't essential, particularly if you enable the deferRender option.

Rather than overloading you with more information I'll stop there, but do ask if you have any follow on questions!

Regards,
Allan

jonmrich

Thanks, Allan.

Still a few more questions.

I've got it working now for the most part. The main idea was to make a single call and to use that data to populate all the tables on my page (around 10). I've got this working now, so that's a huge step forward.

Regarding your answer above, I'm not clear on the suggested changes you have to the HTML. It looks like the same thing that I have now. The alternative you're providing has no rows defined. Is it the case where I don't need to specify these in the HTML, but rather in the DT script and that will still render the right number of columns.

I simplified my actual code for the sake of the example, I'm actually using something like this to define the columns:

 "aoColumns": [
          { "bSortable": true, "width": "0%", "sClass": "lang_body_2", "sTitle": "","visible":false },
          { "bSortable": true, "searchable": false, "width": "10%", "sClass": "lang_body_2 table_names", "sTitle": "" },
          { "bSortable": true, "searchable": false,"width": "20%", "sClass": "lang_body_2",  "sTitle": "Database","visible":false},
          { "bSortable": true, "searchable": false ,"width": "20%","sClass": "lang_body_2","sTitle": "National Average","visible":false },
          { "bSortable": true, "searchable": false ,"width": "50%","sClass": "lang_body_2 index_num table_index","sTitle": "" },
          ],

Also, I would like to use server side (and have used it for another project where there is a ton more data). My question is if I can still do this with a single call. If I use server side and something like below to render the table, will this actually save me client-side processing or is the search still client side? I realize that the way I had it set up, doing server side requires two calls:

First:

$("#total_datatable").DataTable({
          "data":all_data.data,
           "serverSide": true,
      "ajax": {
        "url":"all_data.php",
        "type":"GET"
   }
)}

Then...

var table = $("#total_datatable").DataTable();
table
        .columns( 3 )
        .search( "gender")
        .draw();

I'm wondering what's a better approach.

-server side processing but still making up to 10 calls for the data

-client side processing but only making one call for the data

Right now (with your help), I've got one ajax call that saves all the possible data as a variable and this gets passed into each table function. Seems like that would be better, but not sure.

I'm fairly sure I'm not being completely efficient especially in light of how many tables I have. It's pretty fast now, but I'd like to lower it even more.

Thanks.

allan

The alternative you're providing has no rows defined. Is it the case where I don't need to specify these in the HTML, but rather in the DT script and that will still render the right number of columns.

Exactly this. If there are no elements in the table for what DataTables needs, it will create them. This can actually be quite useful in a case such as this as you can return column information in your Ajax data, so you could do something like:

$('#myTable').DataTable( {
  data: json.data,
  columns: json.columns
} );

You don't get the full range of options this way since JSON can't represent a Javascript function (for use with columns.render for example), but it would let you use columns.title to define the column title if you want to do that.

The other place this comes unstuck is server-side processing... Which leads to:

Also, I would like to use server side (and have used it for another project where there is a ton more data). My question is if I can still do this with a single call.

No - as soon as you involve server-side processing it means multiple Ajax calls. Specifically one Ajax call to the server for every draw (i.e. page change, sort or filter).

If I use server side and something like below to render the table, will this actually save me client-side processing or is the search still client side?

If you want to define an initial column search term with server-side processing you would use the searchCols option. That would save the initial Ajax request being unfiltered and therefore two being needed up front!

In general, if you are considering using server-side processing you need to expect a lot of Ajax requests - the two basically go hand in hand...!

Regards,
Allan