How to add a filter select box for certain column?

andreszs

Hi, I'm adopting DataTables for the company that recently hired me as a multi-purpose developer. So far it works perfectly well and I had no major issues implementing basic and JOINed tables listing by editing and expanding the sample PHP class provided.

However, I'm looking to implement a select box with predefined options to filter results by certain value from a single column. My goal is to add a select box like this one: (added with Corel, this is an edited screenshot)

I'm not sure if a plugin is needed for this, in this case, any suggestion on which plugin to use (and how) will be welcome. Of course I'm using PHP server side processing with AJAX requests for every single database query. Thanks in advance for your cooperation.

colin

One option would be to use collections from the Buttons extension - see example here. Otherwise, you could implement a simple Select control that calls the API directly.

Colin

p.s. sorry about the lost posts, they were caught by the spam filter.

andreszs

Thanks. I decided to create my own select box to filter by column. Here's my example code in case someone else needs to achieve the same result.

var oTable = $('#dataTable').DataTable({
    processing: true,
    responsive: true,
    serverSide: true,
    stateSave: false,
    pageLength: 25,
    order: [0, 'desc'],
    columnDefs: [
        {searchable: false, targets: [2, 5, 8, 9, 10, 12]},
        {orderable: false, targets: [8, 9, 10, 12]}
    ],
    ajax: {
        url: 'get.php',
        type: 'GET',
        error: function (xhr, error, code) {
            console.warn(xhr);
            alert(xhr.responseJSON.message);
        }
    },
    initComplete: function (settings, json) {
        // Add select filter
        $('#dataTable_length').append('<label>&nbsp; App ID:</label>');
        $('#dataTable_length').append('<select class="form-control input-sm"  id="am_aplicacion_id"></select>');
        am_aplicacion_ids = [{0: 'All Apps'}, {1: 'App ID 1'}, {2: 'App ID 2'}];
        for (var key in am_aplicacion_ids) {
            var obj = am_aplicacion_ids[key];
            for (var prop in obj) {
                if (obj.hasOwnProperty(prop)) {
                    $('#am_aplicacion_id').append('<option value="' + prop + '">' + obj[prop] + '</option>');
                }
            }
        }
        // Filter results on select change
        $('#am_aplicacion_id').on('change', function () {
            oTable.columns(2).search($(this).val()).draw();
        });
    }
});

andreszs

Just in case: The PHP side has been modified because column 2 is not searchable, but the select filter will actually search it. So in the PHP script, I edited the filtering code to ask if the column name is X, in which case the filter is added to the WHERE clause.

// Individual column filtering
if(isset($request['columns'])){
    foreach($request['columns'] as $requestColumn){
        $columnIdx = array_search($requestColumn['data'], $dtColumns);
        $column = $columns[$columnIdx];
        $search = $requestColumn['search']['value'];
        if(intval($search) > 0){
            // Add filter to column
            if(!empty($column['db'])){
                if($column['as'] == 'p' and $column['db'] == 'codigo'){
                    // Filter by this column
                    $search = intval($search);
                    $columnSearch[] = "a.aplicacion_id = $search";
                }
            }
        }
    }
}

colin

Nice, thanks for reporting back,

Colin