Dropdown selection using Server Side dataTable | PHP, SQL Server

Question

I have a problem when selecting the dropdown in server-side data table. This is based on this example: https://datatables.net/examples/api/multi_filter_select.html.

I have this guide on how to fix it, but I don't know how to set it up. https://datatables.net/forums/discussion/48780/server-side-column-filtering-with-drop-down-get-all-options

I'm using SQL Server 2012, XAMPP, and connected via SQLSRV.

The data table seems to work fine.

It filters all the result into the dropdown.

My problem is, when I select one of the dropdown, it shows no result (no match found).

This is my code.

For my table.

<div class="box-body">
    <table id="example" class="table table-bordered" style="width:100%">
        <thead>
            <tr>
                <th>Series No</th>
                <th>Account Type</th>
                <th>Tools</th>
            </tr>
        </thead>
        <tfoot>
            <tr>
                <th>Series No</th>
                <th>Account Type</th>
                <th>Tools</th>
            </tr>
        </tfoot>
    </table>
</div>

This is my script

<script>
$(function() {
    $('#example').DataTable( {
        dom: "Bfrtip",
        ajax: {
            url: "account_type_table.php",
            type: "POST"
        },
        serverSide: true,
        columns: [
            { data: "seriesno" },
            { data: "accounttype" },
            { "data": "seriesno", "name": " ", "autoWidth": true, "render": function (data, type, full, meta) {
                return "<button class='btn btn-success btn-sm btn-flat edit' data-id='"+full.seriesno+"'><i class='fa fa-edit'></i> Edit</button> <button class='btn btn-danger btn-sm btn-flat delete' data-id='"+full.seriesno+"'><i class='fa fa-trash'></i> Delete</button>";}
                        }
        ],
        initComplete: function () {
            this.api().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>' )
                } );
            } );
        },
        select: false,
        buttons: [],
    } );
} );

This is my server-side table.

<?php
/*
 * Example PHP implementation used for the index.html example
 */

// DataTables PHP library
include( "../dataTables/table_account_type/DataTables.php" );

// Alias Editor classes so they are easy to use
use
    DataTables\Editor,
    DataTables\Editor\Field,
    DataTables\Editor\Format,
    DataTables\Editor\Mjoin,
    DataTables\Editor\Options,
    DataTables\Editor\Upload,
    DataTables\Editor\Validate,
    DataTables\Editor\ValidateOptions;

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'accounttype' )
    ->fields(
        Field::inst( 'seriesno' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A first name is required' ) 
            ) ),
        Field::inst( 'accounttype' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A last name is required' )  
            ) )
            )
    ->process( $_POST )
    ->json();
?>

What seems to be the problem here? Is it because my filter only works if the data is presented in the code itself, and it does not recognize the data that is from server-side?

Answer 1

Example you are referring to has static data and does filter in UI itself.

But in your case you are using server side processing which means sorting logic should be handle by the server side code.

initComplete method consider data returned by the API and fills the combobox, which is what it need to do.

To make it work as per your requirement

1. Don't use server side processing and load all data at once as given in example

2. If you need to use Server side processing

   You might need to customize initComplete function to call another API which returns all unique columns data and loads it.

   Note: Also you might need to write logic for "when you select a value from combobox"

   I am sure Datatable will add selected combo value as parameter to API and based on that you need to process data in backend and return appropriate response.