Need help with individual column footer select filter with serverside

Stgr

Hi.. I cant figure out how to add my individual column footer select choice to the search filter, and could need some help.
If I didn't use server side (tested with example from this page) the select work just fine, but with my limited knowledge I can't figure out how to add it.
in my php script that the Ajax call I have a sql query like

$query .= "SELECT * FROM table WHERE ";
if(isset($_POST["search"]["value"]))
{
    $query .= '(column1 LIKE "%'.$_POST["search"]["value"].'%"';
    $query .= 'OR column2 LIKE "%'.$_POST["search"]["value"].'%")';
}

with this query I can only use the search box with column1 and column2. I could add the other columns easily but that is not the goal here.
In order for the selects to work should I have to add the selected value to the Ajax call? or is there some other way to filter the table after the table has been loaded? I would prefer that the Ajax call query was just the "select *" and then the build in search function just worked with serverside but im out of ideeas.

This is the call i have now that adds the select on column 7,8,9 and 10.

var dataTable = $('#user_data').DataTable( {
    "processing": true,
    "serverSide": true,
    "ordering":false,
    "ajax":{
        url:"../fetchshift.php",
        type:"POST"
    },
    initComplete: function () {
        this.api().columns([7,8,9,10]).every( function (i) {
            var column = this;
            var select = $('<select class="filter-select'+i+'"><option value="">Show All</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>' )
            } );
        } );
    }
} );

colin

Hi Stgr,

"or is there some other way to filter the table after the table has been loaded?"

Yep, you could disable the serverSide option - that way, all the filtering/paging/etc is performed locally. This would be fine if the dataset is small, but if it was large, this would be cumbersome. This example demonstrates that.

The alternative, as you say, is to modify the server-side PHP script to accommodate searching by individual columns.

Cheers,

Colin

Stgr

I actually thought that i needed serverside if using php to fetch data.. Thanks.. i've solved the problem now..