How do I enable filter dropdown selection, if data is fetched externally?

pansengtat

I was reading this example on filter dropdown selection at http://www.datatables.net/examples/api/multi_filter_select.html and I wish to apply the same concept onto my project, this time the data is being fetched from an external source (MySQL database) via an Editor PHP file.

Inside my JS, I wrote this:

var table = $('#Planning').dataTable( {
                dom: "Tfrtip",
                ajax: "php/PlanningEditor.php",
                columns: [
                    { data: "Schedule.TestRequestNo" },
                    { data: "TestRequestMain.EngineerID" },
                    { data: "Login.Username" },
                    { data: "TestRequestMain.ProjectID" },
                    { data: "TestRequestMain.ProjectName" },
                    { data: "TestRequestAdv.TestPlan" },
                    { data: "TestEnvironment.Name" },
                    { data: "TestRequestAdv.Release" },
                    { data: "Schedule.ScheduledDate" },
                    { data: "Schedule.ScheduledManHrsNM" },
                    { data: "Schedule.ScheduledManHrsOT" },
                    { data: "Schedule.ScheduledManHrsOTSunPH" },
                    { data: "ConfirmationMirror.String" },
                    { data: "TestRequestMain.Status" },
                    { data: "StatusMirror.String" },
                    { data: "TestRequestAdv.RemarksOutsource" },
                    { data: "TestRequestMain.AssignedGroupID" },
                    { data: "AssignedGroup.Name" },
                    { data: "Schedule.ID" }
                ],
                "order": [[ 2, "desc" ]],
                "columnDefs": [
                    {
                        "targets": [ 1 ],
                        "visible": false,
                        "searchable": false
                    },
                    {
                        "targets": [ 13 ],
                        "visible": false,
                        "searchable": false
                    },
                    {
                        "targets": [ 16 ],
                        "visible": false,
                        "searchable": false
                    },
                    {
                        "targets": [ 18 ],
                        "visible": false,
                        "searchable": false
                    }
                ],
                tableTools: {
                    sRowSelect: "os",
                    sSwfPath: "extensions/tabletools/swf/copy_csv_xls_pdf.swf",
                    aButtons: [
                        {   sExtends:       "editor_edit",   
                            editor:         editor,
                            sButtonText:    "Schedule"
                        },
                        {   sExtends:       "xls",
                            sButtonText:    "Export as Excel",
                            sAction:        "flash_save"
                        },
                        {   sExtends:       "csv",
                            sButtonText:    "Export as CSV",
                            sAction:        "flash_save"
                        },
                        {   sExtends:       "pdf",
                            sButtonText:    "Export as PDF",
                            sPdfOrientation:"landscape"
                        },
                        "print"
                    ]
                },
                "deferRender": true,
                initComplete: function(settings, json) {
                    editor.field('Schedule.Approval').update(json.ConfirmationMirror);
                }
            } );

I added this to my JS:

$("#Planning tfoot th").each( function ( i ) {
                var select = $('<select><option value=""></option></select>')
                    .appendTo( $(this).empty() )
                    .on( 'change', function () {
                        var val = $(this).val();
         
                        table.column( i )
                            .search( val ? '^'+$(this).val()+'$' : val, true, false )
                            .draw();
                    } );
         
                table.column( i ).data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' )
                } );
            } );

But what I got instead was this message inside the Developer Console Log:

Uncaught TypeError: undefined is not a function

And it is pointing to this line:

 table.column( i ).data().unique().sort().each( function ( d, j ) {

Another error is that the dropdown box only appears for the first column, and it has no dropdown options.

I am not sure what I am missing.

Any thoughts on resolving this, please?

EDIT: I just realised that if the "dataTable" has its "d" capitalized, i.e. becomes "DataTable()" instead, the console warning disappears. And while now every footer will have its own dropdown filter, none of them will have any data filled-in. Any ideas to resolve the issue of missing dropdown values for this case?

pansengtat

I have found a partial solution.

The reason that I said that it is partially resolved, is because implementing this solution will cause filters for hidden columns to still appear.

Partial Solution:

I modified the code for creating the filter dropdown select(s) to after the document.ready() event, inside this event setTimeout(function() {}, 1);

JS CODE:

setTimeout(function() {
            // call your code here that you want to run after all $(document).ready() calls have run
            //--- START: Footer - Individual column searching (select inputs) ---//
            $("#TRplanning tfoot th").each( function ( i ) {
                var select = $('<select><option value=""></option></select>')
                    .appendTo( $(this).empty() )
                    .on( 'change', function () {
                        var val = $(this).val(); 
         
                        table.column( i )
                            .search( val ? '^'+$(this).val()+'$' : val, true, false )
                            .draw();
                    } );
                table.column( i ).data().unique().sort().each( function ( d, j ) {
                    select.append( '<option value="'+d+'">'+d+'</option>' );
                } );
            } );
            //--- END: Footer - Individual column searching (select inputs) ---//
        }, 1);

I still need a little bit of help in hiding the filters for the columns that are meant to be hidden.

Any suggestions and advice on how to hide column filters based on DataTable() initializatio n would be greatly appreciated! Thanks!

pansengtat

I am going to bump this thread to get some attention and see if I could gather some help/thoughts on this.

In the event setTimeout(function() {}, 1);, the code $("#TRplanning tfoot th").each() is executed for every single footer of the table.

Is there a way to specifically target only some of the footers, based on the columns that I chose to hide?

NOTE: The following code snippet will not display the filters correctly, because the last 2 columns will have missing <th></th> and thus have missing filters.

setTimeout(function() { //--- Call your code that you want to run after all $(document).ready() calls have run, here. ---//
            $("#TRplanning tfoot th").each( function ( i ) {
                if (i != 1 && i != 13 && i != 16 && i != 18)
                {
                    var select = $('<select><option value=""></option></select>')
                        .appendTo( $(this).empty() )
                        .on( 'change', function () {
                            var val = $(this).val(); 
             
                            table.column( i )
                                .search( val ? '^'+$(this).val()+'$' : val, true, false )
                                .draw();
                        } );
                    table.column( i ).data().unique().sort().each( function ( d, j ) {
                        select.append( '<option value="'+d+'">'+d+'</option>' );
                    } );
                }
                             else
                {
                    $(this).remove();
                }
            } );
        }, 1);

allan

Rather than using:

$("#TRplanning tfoot th").each( function ( i ) {

Try using the DataTables columns() method (and specifically columns().nodes() to get the nodes).

That way you can specify if you want the visible column, hidden columns or both.

Allan

pansengtat

I already read through the documentation on columns() and columns().nodes(), but was unable to convert the existing code into the form of columns().

I did, however, found a workaround but comes with a new problem: even though the columns are initialized correctly, I suspect that the table is being auto-resized, thus not showing the last two columns.

setTimeout(function() { //--- Call your code that you want to run after all $(document).ready() calls have run, here. ---//
    $("#TRplanning tfoot th").each( function ( i ) {
    {
        var select = $('<select><option value=""></option></select>')
                  .appendTo( $(this).empty() )
                  .on( 'change', function () {
                    var val = $(this).val(); 
             
                    table.column( i )
            .           search( val ? '^'+$(this).val()+'$' : val, true, false )
                        .draw();
                        } );
        table.column( i ).data().unique().sort().each( function ( d, j ) {
            select.append( '<option value="'+d+'">'+d+'</option>' );
            } );
        }
    } );

    table.column(1).visible(false);
    table.column(13).visible(false);
    table.column(16).visible(false);
    table.column(18).visible(false);

}, 1);

NOTE: In order for this code snippet to work, the following code in the Datatables instance should be commented-out:

"columnDefs": [
                    {
                        "targets": [ 1 ],
                        "visible": false,
                        "searchable": false
                    },
                    {
                        "targets": [ 13 ],
                        "visible": false,
                        "searchable": false
                    },
                    {
                        "targets": [ 16 ],
                        "visible": false,
                        "searchable": false
                    },
                    {
                        "targets": [ 18 ],
                        "visible": false,
                        "searchable": false
                    }
                ],

Using Chrome > Dev Tools > Inspect, the HTML elements for the "missing" two columns are present, it's just not shown (I did not hide the last 2 columns for sure, and the indices for what was meant to not being shown are correct).

EDIT: I realised that there is a way to auto-resize tables as seen in (http://www.datatables.net/examples/basic_init/flexible_width.html) by setting the width in the HTML file to 100%, like so:

<table id="TRplanning" class="display dataTable" role="grid" width="100%" cellspacing="0">