Kumaran S Kumaran S - 1 month ago 7
Ajax Question

DataTables multi-filter-select not populating dropdown

I'm creating a DataTables table where data comes from a AJAX call and it loads fine. The search works fine as well. When I add the code for the multi-filter-select, the drop-downs appear but they are empty. I'm using latest library for both jQuery and DataTables.

JS

function overview() {
$.ajax({
url: "/_inc/_ajax/ajax.tables.php",
dataType: 'json',
success: function(results) {

var table = $('#overviewTable').DataTable
({
initComplete: function () {
this.columns().every( function () {
var column = this;
var select = $('<select><option value=""></option></select>')
.appendTo( $(column.footer()).empty() )
.on( 'change', function () {
var val = $.fn.dataTable.util.escapeRegex(
$(this).val()
);

column
.search( val ? '^'+val+'$' : '', true, false )
.draw();
} );

column.data().unique().sort().each( function ( d, j ) {
select.append( '<option value="'+d+'">'+d+'</option>' )
} );
} );
}
});

table.clear();
for(var i = 0; i < results["id"].length; i++) {
table.row.add(
[
results["id"][i],
results["title"][i],
results["Tier"][i],
results["region"][i],
results["2016"][i],
results["2017"][i],
results["Letter_Type"][i],
results["Change_Type"][i],
]
).draw();
}
},
error: function (xhr, ajaxOptions, thrownError) {
alert("ERROR: " + xhr.responseText + " - " + thrownError);
}
});
}


HTML

<table id="overviewTable" class="table table-striped table-bordered" cellspacing="0" width="100%">
<thead>';
<tr>
<th>ID</th>
<th>TITLE</th>
<th>TIER</th>
<th>REGION</th>
<th>2016</th>
<th>2017</th>
<th>LETTER TYPE</th>
<th>CHANGE TYPE</th>
</tr>
</thead>
<tfoot>
<tr>
<th>ID</th>
<th>TITLE</th>
<th>TIER</th>
<th>REGION</th>
<th>2016</th>
<th>2017</th>
<th>LETTER TYPE</th>
<th>CHANGE TYPE</th>
</tr>
</tfoot>
</table>';

Answer

I managed to figure out why it was not working. I had to use ajax syntax used by DataTables. More information can be found at DataTables. Here is the code:

JS

function overview() {
var table = $('overviewTable').DataTable
({
    "ajax":         {
        "url": "/_inc/_ajax/ajax.tables.php",
        "dataSrc": "",
    },
    "columns":      [
        { "data": "id" },
        { "data": "title" },
        { "data": "tier" },
        { "data": "region" },
        { "data": "2016" },
        { "data": "2017" },
        { "data": "Letter_Type" },
        { "data": "Change_Type" },
    ],
    initComplete: function () {
        var columnsCustom = [2, 3, 6, 7];
        var columnNames = ["Tier", "Region", "Letter Type", "Change Type"];
        var columnId = ["tier", "region", "letterType", "changeType"];
        for (var i = 0; i < 4; i++) {
            this.api().columns(columnsCustom[i]).every( function () {
                var column = this;
                var select = $('<label for="' + columnId[i] +'" class="sr-only">' + columnNames[i] +'</label><select id="' + columnId[i] +'"><option value=""></option></select>')
                    .appendTo( $(column.footer()).empty() )
                    .on( 'change', function () {
                        var val = $.fn.dataTable.util.escapeRegex(
                            $(this).val()
                        );

                        column
                            .search( val ? '^' + val + '$' : '', true, false )
                            .draw();
                    } );

                column.data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="' + d + '">' + d + '</option>' )
                } );
            } );
        }
    },
});

}

HTML

<table id="overviewTable" class="table table-striped table-bordered" cellspacing="0" width="100%">
<thead>';
    <tr>
        <th>ID</th>
        <th>TITLE</th>
        <th>TIER</th>
        <th>REGION</th>
        <th>2016</th>
        <th>2017</th>
        <th>LETTER TYPE</th>
        <th>CHANGE TYPE</th>
     </tr>
 </thead>
 <tfoot>
     <tr>
        <th>ID</th>
        <th>TITLE</th>
        <th>TIER</th>
        <th>REGION</th>
        <th>2016</th>
        <th>2017</th>
        <th>LETTER TYPE</th>
        <th>CHANGE TYPE</th>
      </tr>
  </tfoot>

Comments