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?

MyTwoCents · Accepted Answer · 2019-08-12 06:41:51Z

3

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

Don't use server side processing and load all data at once as given in example
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.

answered Aug 12, 2019 at 6:41

MyTwoCents

7,6723 gold badges28 silver badges56 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

pjustindaryll Over a year ago

That's painful, alot of editing on the main code. I'll look into this. Thank you!

Collectives™ on Stack Overflow

Dropdown selection using Server Side dataTable | PHP, SQL Server

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related