使用来自查询的参数动态创建数据表 jQuery

我使用"datatables plugin for jquery"在mysql中显示查询结果。

用户还可以从下拉列表 （dateParam） 和多选值 （conseiller） 中进行选择，这些值将自动限制查询结果，从而修改表。例如，我没有 5 列，而是只有 3 列。

这是我收到以下错误警报的地方："...请求的未知参数"data.1"（例如）用于第 0 行..."。

JSON 结果（不选择）：

[{"name":"CONSEILLER","data":["cons1","cons2","cons3","TOTAL UNITES"]},{"name":"TOTAL UNITES","data":[1,9,2,12]}]

如果我选择一些参数，则json结果：

[{"name":"CONSEILLER","data":["cons2","TOTAL UNITES"]},{"name":"TOTAL UNITES","data":[9,9]}]

这是我查询的一部分：

<?php
    include("../dbconfig.php");
    if (isset($_GET["dateParam"],$_GET["dateParam2"],$_GET["conseiller"])) {
    $SQL = "
        ...the query...
    ";  
    $result = $dbh->prepare($SQL);
    $result->execute();
    } else {
    $SQL = "
        ...the query....
    ";  
    $result = $dbh->prepare($SQL);
    $result->execute();
    }   
    $rows = array();
    $rows['name'] = 'CONSEILLER';
    $rows1 = array();
    $rows1['name'] = 'TOTAL UNITES';
    while($row = $result->fetch()) {
         $rows['data'][] = $row['CONSEILLER'];
         $rows1['data'][] = ($row['UNITES']);
    }
    $result = array();
    array_push($result,$rows);
    array_push($result,$rows1);
    header('Content-type: application/json');
    print json_encode($result, JSON_NUMERIC_CHECK); // VERSION PHP >= 5.3.3
?>

这是我的js的一部分：

$(document).ready(function() {
var table = $('#dt_actions_rae_conseiller').DataTable({
        "paging": false,
        "searching": false,
        "bInfo": false,
        "scrollX": true,
        "bProcessing": true,
        "bServerSide": true,
        "sAjaxDataProp": "",
        "sAjaxSource": "../query/query_actions_rae_conseiller_dt.php",
        "aoColumns": [
            { "data": "name" },
            { "data": "data.0" },
            { "data": "data.1" },
            { "data": "data.2" },
            { "data": "data.3" },
            ],
    });
});
/**
    * Datepicker
*/
$(function() {
        var dateParam = "";
        $( "#datepicker" ).datepicker({
            dateFormat: "yy-mm-dd",
            showAnim: 'drop',
            //showOn: "button",
            //buttonImage: "../icones/calendar.gif",
            //buttonImageOnly: true,
            onSelect: function(date){
                dateParam = date;
                }
        });
        var dateParam2 = "";
        $( "#datepicker2" ).datepicker({
            dateFormat: "yy-mm-dd",
            showAnim: 'drop',
            //showOn: "button",
            //buttonImage: "../icones/calendar.gif",
            //buttonImageOnly: true,
            onSelect: function(date){
                dateParam2 = date;
                }
        });
});
/**
* Multiselect
*/
    $(function(){ 
        $("#conseiller").multiselect({
            //header: 'Choisir conseillers',
            minWidth: 160,
            checkAllText: 'all',
            uncheckAllText: 'no',
            noneSelectedText: 'Conseiller',
            selectedList: 7,
            /* selectedText: function(numChecked, numTotal, checkedItems){
                return numChecked + ' of ' + numTotal + ' checked';
            }, */
            show: ["bounce", 200],
            hide: ["explode", 1000]
        });
        //$('#projet').bind('change', function() {alert('Change'); });  
    });
/**
    * Button
*/
$(function(){
    $('button').click(function() {
        var d1 = $("#datepicker").val();
        var d2 = $("#datepicker2").val();
        var d3 = $("#conseiller").val().join(",");
        $.ajax({
                url: "../query/query_actions_rae_conseiller_dt.php",
                //data: {dateParam:d1, dateParam2:d2},
                type: "get",
                dataType: "json",
                success: function(json){
                            table = $('#dt_actions_rae_conseiller')
                            .on('preXhr.dt', function ( e, settings, data ) {
                            data.dateParam = d1
                            data.dateParam2 = d2
                            data.conseiller = d3
                            })
                            .DataTable({
                                "destroy": true, // TO REINITIALISE DATATABLE
                                "paging": false,
                                "searching": false,
                                "bInfo": false,
                                "scrollX": true,
                                "bProcessing": true,
                                "bServerSide": true,
                                "sAjaxDataProp": "",
                                "sAjaxSource": "../query/query_actions_rae_conseiller_dt.php",
                                "aoColumns": [
                                { "data": "name" },
                                { "data": "data.0" },
                                { "data": "data.1" },
                                { "data": "data.2" },
                                { "data": "data.3" },
                                ],
                            }); 
                        } // end ajax function
        }); // end ajax
    }); // end click function
}); // end function

和我的PHP：

<table id="dt_actions_rae_conseiller" class="table table-striped table-bordered" cellspacing="0" width="100%">
            <thead> // not needed ?
                <tr> // not needed ?
                    <th></th> // not needed ?
                    <th></th> // not needed ?
                    <th></th> // not needed ?
                    <th></th> // not needed ?
                    <th></th> // not needed ?
                </tr> // not needed ?
            </thead> // not needed ?
</table>

感谢所有人！