动态获取数据表中 $aColumns 数组中的列名

2022-01-01 00:00:00 json jquery-plugins php codeigniter jquery-datatables

首先我会提到我想要实现的目标.我正在使用 PHP 的 CodeIgniter 框架.我的数据库中有 5 个表，我想在显示页面上单击按钮以 Datatables 格式显示它们.我使用服务器端处理 php 作为数据源.所以一开始我编写了只显示一个Datatable格式的表格的代码，并且成功了.现在我想在按钮单击事件中一次显示 5 个表中的一个表.但是 $aColumns 长度应该等于 HTML 表中定义的列数.现在考虑标记表，它有 4 列 student_id、exam_id、subject_id 和marks_achieved.现在另一个表是 branch 并且只有 2 列 branch_id 和 branch_name.所以我不能动态增加或减少 HTML 中的标签，所以我很困惑.我也使用这个 source 创建数据表.您可以在此处查看我的 getTable() 函数.>

jQuery:

$(document).ready(function(){$('#datatable').dataTable({"sPaginationType":"full_numbers","bJQueryUI":true,bProcessing":真，bServerSide":真，"sServerMethod": "GET","sAjaxSource": "数据表/获取表","iDisplayStart": 0,iDisplayLength":10，"aLengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],"asorting": [[0, 'asc']],"aoColumns": [{ "bVisible": true, "bSearchable": true, "bSortable": true },{ "bVisible": true, "bSearchable": true, "bSortable": true },{ "bVisible": true, "bSearchable": true, "bSortable": true },{ "bVisible": true, "bSearchable": true, "bSortable": true }]})$('input[type=button]').bind('click', function(){var param = $(this).attr('id');数据 = 参数 + '=1';$.ajax({类型:'POST'，网址:'数据表'，数据:数据}).完成(功能(数据){控制台日志(数据)；$('#display_area').html(数据);});})});

HTML:

<script type="text/javascript" src="../js/jquery.js"></script><script type="text/javascript" src="../js/javascript.js"></script><script type="text/javascript" src="../js/jquery.dataTables.min.js"></script><body id="dt_example"><form action="" method="post"><input type="button" id="display_branch" name="display_branch" value="显示分支表" ><input type="button" id="display_marks" name="display_marks" value="显示标记表" ></表单><div id="容器"><div id="演示"><table id="datatable" cellpadding="0" cellspacing="0" border="0" width="100%"><头><tr><th>学生ID</th><th>考试ID</th><th>主题ID</th><th>取得的成绩</th></tr></thead><tr><td></td><td></td><td></td><td></td></tr></tbody><tfoot></tfoot><div class="spacer"></div>

为了动态获取列，我在 datatable.php 中进行了如下更改，但它不起作用.这里有什么问题，或者我应该尝试其他方法?

 if(isset($_POST['display_marks'])){$aColumns = array('student_id', 'exam_id', 'subject_id', 'marks_achieved');$sTable = '标记';}if(isset($_POST['display_branch'])){$aColumns = array('branch_id', 'branch_name');$sTable = '分支';}

user1190992 发布的解决方案有效，但整个方法发生了变化.并且我想清理列的标题.显示branch_id"而不是我想显示分支 ID.我该如何进行这种消毒?

解决方案

这是从 JSON 数据动态创建 HTML 的一种非常简单的方法.不过它不使用服务器端处理.

JavaScript

$(document).ready(function() {$(".abutton").click(function() {$('#myDatatable_wrapper').detach();//删除现有表var table = '';$.ajax({网址:'dt.php'，数据:"table_id="+$(this).attr("id"),类型:POST"，成功:功能(数据){$.each(data.aoColumns, function(key, value) {table += "";});table += "</tr></thead><tbody>";$.each(data.aaData, function(key, row) {table += "";$.each(row, function(key, fieldValue) {table += "";});table += "</tr>";});table += '"+value+"
"+fieldValue+"
';$('.container').html(table);$('#myDatatable').dataTable();},数据类型:json"});});});

PHP

$table_id = filter_input(INPUT_POST, "table_id", FILTER_SANITIZE_STRING);$dbconn = mysqli_connect("localhost", "用户名", "密码");if($table_id == "table1") {$sql_query = mysqli_query($dbconn, 'SELECT * FROM display_branch');}别的 {$sql_query = mysqli_query($dbconn, 'SELECT * FROM display_marks');}如果(mysqli_num_rows($sql_query)==0){echo "检查你的身份证";退出(1)；}$data = 数组();$data['aaData'] = array();while($row = mysqli_fetch_assoc($sql_query)) {$data['aaData'][] = $row;}$data['aoColumns'] = array();while($finfo = mysqli_fetch_field($sql_query)) {$data['aoColumns'][] = $finfo->name;}回声 json_encode($data);

HTML

<button id="table1" class="abutton">Table 1</button><br/><button id="table2" class="abutton">Table 2<;/按钮><div class="container"></div>

希望这会有所帮助.

First I'll mention what I am trying to achieve. I am using CodeIgniter framework of PHP. I have 5 tables in my database and I want to display them in Datatables format on a button click on the display page. I am using server side processing php as data source. So at first I made the code for displaying only one table in Datatable format and was successful in it. Now I want to display one table at a time out of 5 on button click event. But $aColumns length should be equal to number of columns defined in HTML table. Now considering marks tabe, it has 4 columns student_id, exam_id, subject_id and marks_achieved. Now another table is branch and has 2 columns only branch_id and branch_name. So I cannot increase or decrease tags in HTML dynamically so I am confused. Also I am using this source to create datatables. You can check my getTable() function here.

jQuery:

$(document).ready(function()
{
$('#datatable').dataTable({
    "sPaginationType":"full_numbers",
    "bJQueryUI":true,
    "bProcessing": true,
    "bServerSide": true,
    "sServerMethod": "GET",
    "sAjaxSource": "datatable/getTable",
    "iDisplayStart": 0,
    "iDisplayLength": 10,
    "aLengthMenu": [[10, 25, 50, -1], [10, 25, 50, "All"]],
    "aaSorting": [[0, 'asc']],
    "aoColumns": [
        { "bVisible": true, "bSearchable": true, "bSortable": true },
        { "bVisible": true, "bSearchable": true, "bSortable": true },
        { "bVisible": true, "bSearchable": true, "bSortable": true },
        { "bVisible": true, "bSearchable": true, "bSortable": true }
        ]
})

$('input[type=button]').bind('click', function(){
        var param = $(this).attr('id');
            data = param + '=1';

        $.ajax({
            type: 'POST',
            url: 'datatable', 
            data: data                
        }).done(function( data ) { 
            console.log(data);
            $('#display_area').html(data); 
        });
    })
    });

HTML:

<script type="text/javascript" src="../js/jquery.js"></script>
<script type="text/javascript" src="../js/javascript.js"></script>
<script type="text/javascript" src="../js/jquery.dataTables.min.js"></script>
</head>
<body id="dt_example">
<form action="" method="post">
<input type="button" id="display_branch" name="display_branch" value="Display Branch Table" >
<input type="button" id="display_marks" name="display_marks" value="Display Marks Table" >
</form>   
<div id="container">
<div id="demo">
<table id="datatable" cellpadding="0" cellspacing="0" border="0" width="100%">
<thead>
    <tr>
        <th>Student ID</th>
        <th>Exam ID</th>
        <th>Subject ID</th>
        <th>Marks Achieved</th>
    </tr>
</thead>
<tbody>
    <tr>
        <td></td>
        <td></td>
        <td></td>
        <td></td>
    </tr>
</tbody>
<tfoot></tfoot>
</table>
</div>
<div class="spacer"></div>
</div>
</body>
</html>

To get the columns dynamically I have made this change as shown below in datatable.php but it is not working. What is wrong here or I should try some other approach ?

   if(isset($_POST['display_marks']))
    {
        $aColumns = array('student_id', 'exam_id', 'subject_id', 'marks_achieved');
        $sTable = 'marks';
    }
    if(isset($_POST['display_branch']))
    {
        $aColumns = array('branch_id', 'branch_name');
        $sTable = 'branch';
    }

EDIT: The solution posted by user1190992 works but the whole approach is changed. And in that I want to sanitize the headers of the columns. "branch_id" is displayed instead I want to display Branch ID. How can I perform this sanitization ?

解决方案

This is a very simple way for creating HTML from JSON data dynamically. It doesn't use server side processing though.

JavaScript

$(document).ready(function() {
    $(".abutton").click(function() {
        $('#myDatatable_wrapper').detach(); //Remove existing table
        var table = '<table id="myDatatable" class="table"><thead><tr>';
        $.ajax({
            url: 'dt.php',
            data: "table_id="+$(this).attr("id"),
            type: "POST",
            success: function (data) {
                $.each(data.aoColumns, function(key, value) {
                    table += "<th>"+value+"</th>";
                });
                table += "</tr></thead><tbody>";
                $.each(data.aaData, function(key, row) {
                    table += "<tr>";
                    $.each(row, function(key, fieldValue) {
                        table += "<td>"+fieldValue+"</td>";
                    });
                    table += "</tr>";
                });
                table += '<tbody></table>';
                $('.container').html(table);
                $('#myDatatable').dataTable();
            },
            dataType: "json"
        });
    });
});

PHP

$table_id = filter_input(INPUT_POST, "table_id", FILTER_SANITIZE_STRING);
$dbconn = mysqli_connect("localhost", "username", "password");

if($table_id == "table1") {
    $sql_query = mysqli_query($dbconn, 'SELECT * FROM display_branch');
}
else {
    $sql_query = mysqli_query($dbconn, 'SELECT * FROM display_marks');
}

if(mysqli_num_rows($sql_query) == 0) {
    echo "Check your ID";
    exit(1);
}
$data = array();
$data['aaData'] = array();
while($row = mysqli_fetch_assoc($sql_query)) {
    $data['aaData'][] = $row;
}

$data['aoColumns'] = array();
while($finfo = mysqli_fetch_field($sql_query)) {
    $data['aoColumns'][] = $finfo->name;
}
echo json_encode($data);

HTML

<button id="table1" class="abutton">Table 1</button><br /><button id="table2" class="abutton">Table 2</button>
<div class="container"></div>

Hope this helps.

相关文章