使用服务器端数据表的下拉菜单选择PHP，SQL Server [英] Dropdown selection using Server Side dataTable | PHP, SQL Server

问题描述

在服务器端数据表中选择下拉菜单时出现问题.这基于以下示例: https://datatables.net/examples/api/multi_filter_select.html .

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.

我有关于如何修复它的指南，但是我不知道如何设置它. https://datatables.net/forums/discussion/48780/server-side-column-filtering-with-drop-down-get-all-options

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

我正在使用SQL Server 2012，XAMPP，并通过SQLSRV连接.

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

数据表似乎工作正常.

它将所有结果过滤到下拉菜单中.

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).

这是我的代码.

在我的桌子上.

<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>

这是我的剧本

<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: [],
    } );
} );

这是我的服务器端表.

<?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?

推荐答案

示例您所指的是具有静态数据，并且会在UI本身中进行过滤.

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方法考虑API返回的数据并填充组合框，这是需要做的.

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

按照您的要求进行操作

1. 不要使用服务器端处理，而是按照例子

如果需要使用服务器端处理

If you need to use Server side processing

您可能需要自定义initComplete函数来调用另一个API，该API返回所有唯一列数据并加载它.

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"

我确定Datatable会将选定的组合值作为参数添加到API，并基于此您需要在后端处理数据并返回适当的响应.

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.

这篇关于使用服务器端数据表的下拉菜单选择PHP，SQL Server的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！