Geek Answers Handbook

Dynamically get column names in $ aColumns arrary in datatables

First I will talk about what I'm trying to achieve. I am using the CodeIgniter framework for PHP. I have 5 tables in my database and I want to display them in Datatables format by clicking a button on the display page. I am using php server processing as a data source. Therefore, at first I made a code to display only one table in the Datatable format and was successful in this. Now I want to display one table at a time out of 5 on a button click event. But the length of $ aColumns must be equal to the number of columns defined in the HTML table. Now, given the tabe labels, it has 4 columns student_id, exam_id, subject_id and marks_achieved. Now another table is a branch and has 2 columns only branch_id and package_name. Therefore, I cannot increase or decrease tags in HTML dynamically, so I got confused. In addition, I use 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 made this change as shown in the datatable.php file below, but it does not work. What is wrong here, or should I try a different 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 user 1190992 works, but the whole approach is changed. And in this I want to sanitize the column headings. Instead, "branch_id" is displayed. I want to display the branch id. How can I do this sanitation?

+4
source share
1 answer

This is a very simple way to dynamically create HTML from JSON data. However, it does not use server-side processing.

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.

+2
source

More articles:


All Articles