StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POExporting filtered DataTable with serverside Processing (CodeIgniter)
primarykey
Id
20158111
data
AcceptedAnswerId
20325249
AnswerCount
1
ClosedDate
CommentCount
1
CommunityOwnedDate
CreationDate
2013-11-23T03:21:09.263
FavoriteCount
0
LastActivityDate
2014-02-27T07:39:54.983
LastEditDate
2013-11-23T03:40:02.760
LastEditorUserId
848423
OwnerUserId
848423
ParentId
0
PostTypeId
1
Score
0
ViewCount
3164
LastEditorDisplayName
text
Body
<p>I'm working with DataTables and CodeIgniter, and I've been working on exporting a table after filtering it by each column. </p> <p>Since we're going to be using a lot of rows of data, I started working on the version where all of the processing (filtering, pagination, search, etc.) was based on the server. I'm currently having trouble exporting my <em>filtered table</em> into a PDF. </p> <p>Issue that arise:</p> <ul> <li>Only the rows that are currently visible to the user are exported for download. None of the other rows (especially those on other pages--separated through pagination) are included.</li> <li>If the number of rows I get after filtering is > the number of rows allowed per page, I don't get all of the rows that I narrowed down my table to. </li> </ul> <p><em>I would like some help as to how I would overcome this issue.</em> </p> <p>My code is as follows: <strong>Views/List.php</strong></p> <pre><code><script> $(document).ready( function () { var oTable = $('#tablesupport').dataTable({ "sPaginationType":"bootstrap", "oLanguage": { "sSearch": "Search all columns:" }, "sScrollY": "400px", "bProcessing": true, "bServerSide": true, "sServerMethod": "GET", "sAjaxSource": "dthelper", "iDisplayLength": 10, "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 }, { "bVisible": true, "bSearchable": true, "bSortable": true }, { "bVisible": true, "bSearchable": true, "bSortable": true } ] }); //This function works completely fine if the table is processed client-side, //I am not as successful on the server-side function table2xls(oTable, exportmode, tableElm){ var xls = ''; var headers = []; var rows = []; xls +="<table><thead><tr>"; //Headers do not appear when server-side processing is used $(tableElm+' thead').find('th').each(function() { var $th = $(this); var text = $th.text(); var header = '<th>' + text + '</th>'; // headers.push(header); // original code if(text != "") headers.push(header); }); xls += headers.join(" "); xls += '<tbody><tr>'; // get table data if (exportmode == "full") { // total data var total = oTable.fnSettings().fnRecordsTotal(); for(i = 0; i < total; i++) { var row = oTable.fnGetData(i); row = strip_tags(row); rows.push(row); } } else { // This branch works fine, however this does not work (in serv. side) for // rows on other pages separated by pagination var filteredrows = $("#tablesupport").dataTable()._('tr', {"filter": "applied"}); for ( var i = 0; i < filteredrows.length; i++ ) { var numCols = 6; var col = '<tr>'; for(var j = 0; j < numCols; j++){ if(j==2) col+= '<td><div style="font-family: Mv Iyyu Formal;">' + filteredrows[i][j] + '</div></td>'; else col += '<td>' + filteredrows[i][j] + '</td>'; } //console.log(filteredrows[i][0]); col += '</tr>'; rows.push(col); }; /* $(tableElm+' tbody td:visible').each(function(index) { var row = oTable.fnGetData(this); row = strip_tags(row); row = '<td>' + row + '</td>'; rows.push(row); }); */ } xls += rows.join(); xls += "</tr></tbody></table>"; //alert(xls); $("#xlsinput").val(xls); } function strip_tags(html) { var tmp = document.createElement("div"); tmp.innerHTML = html; return tmp.textContent||tmp.innerText; } $("tfoot input").keyup( function () { /* Filter on the column (the index) of this element */ oTable.fnFilter( this.value, $("tfoot input").index(this) ); } ); $.extend( $.fn.dataTableExt.oStdClasses, { "sSortAsc": "header headerSortDown", "sSortDesc": "header headerSortUp", "sSortable": "header" } ); $('#tablesupport tr').click(function() { var href = $(this).find("a").attr("href"); if(href) { window.location = href; } }); $('#export_visible_xls').click(function(event) { event.preventDefault(); table2xls(oTable, 'visible', 'table#tablesupport'); }); } ); </script> <div class="container"> <div class="row-fluid"><div class="span12"> <div class="span3"> <div class="btn-group" style="position:relative; right:170px;"> <a class="btn dropdown-toggle" data-toggle="dropdown" href="#"> Actions <span class="caret"></span> </a> <ul class="dropdown-menu"> <li><a href="#" id="export_visible" value="CSV" />CSV</a></li> <li><a href="#" id="export_visible_pdf" value="PDF">PDF</a></li> <li><a href="#" id="export_visible_xls" value="XLS" >XLS</a></li> </ul> </div> </div> <div id="down-button"> </div> <div class="span3"> <form action=<?php echo base_url() . 'exports/toxls/member_export'; ?> method="post"> <input type="hidden" id="xlsinput" name="xlspost" /> <button type="submit" style="display: none; position: relative; right: 300px;" id="xlsdownload" name="submit" value="Download" class="btn btn-danger" > <i class="icon-download" ></i> Download</button> </form> </div> <div class="span3"> <form action=<?php echo base_url() . 'exports/topdf'; ?> method="post"> <input type="hidden" id="pdfinput" name="pdfpost"></input> <button type="submit" style="display: none; position: relative; right: 500px;" id="pdfdownload" name="submit" value="Download" class="btn btn-info"> <i class="icon-download" ></i> Download</button> </form> </div> <div class="span3"> <form action=<?php echo base_url() . 'exports/tocsv/member_export'; ?> method="post"> <input type="hidden" id="csvinput" name="csvpost"/> <button type="submit" style="display: none; position: relative; right: 710px" id="csvdownload" name="submit" value="Download" class="btn btn-primary"> <i class="icon-download" ></i> Download</button> </form> </div></div></div></div> <div class="row-fluid"> <div class="span12"> <div class="span2"></div> <div class="span8"> <div class="table-responsive"> <div id="dvData"> <table class = "table table-hover" id="tablesupport"> <thead> <tr> <th id='nid'>NID</th> <th>Name (ENG)</th> <th>Name (DV)</th> <th>Current address</th> <th>Permanent address</th> <th>Status</th> </tr> </thead> <tbody id="fbody" class="rowlink"> </tbody> <tfoot> <tr> <th><input type="text" name="search_nid" placeholder="Search NID" class="search_init" /></th> <th><input type="text" name="search_name" placeholder="Search Name (ENG)" class="search_init" /></th> <th><input type="text" name="search_namedv" id="thaanaInput7" class="thaana" placeholder="ނަން ހޯދާ" class="search_init" /></th> <th><input type="text" name="search_caddress" placeholder="Search curr. address" class="search_init" /></th> <th><input type="text" name="search_paddress" placeholder="Search perm. address" class="search_init" /></th> <th><input type="text" name="search_status" placeholder="Search status" class="search_init" /></th> </tr> </tfoot> </table> </div></div><div class="span2"></div></div></div> </code></pre> <p><strong>Controllers/Record/dthelper()</strong> function</p> <pre><code>public function dthelper(){ $numCols = 6; $this->load->database(); $aColumns = array('member_NID', 'member_fName', 'member_mName', 'member_lName', 'member_fNameDV', 'member_mNameDV', 'member_lNameDV', 'member_currAtoll', 'member_currIsland','member_currAddress', 'member_permAtoll', 'member_permIsland', 'member_permAddress', 'member_status'); $sTable = "membershiprecord"; $iDisplayStart = $this->input->get_post('iDisplayStart', true); $iDisplayLength = $this->input->get_post('iDisplayLength', true); $iSortCol_0 = $this->input->get_post('iSortCol_0', true); $iSortingCols = $this->input->get_post('iSortingCols', true); $sSearch = $this->input->get_post('sSearch', true); $sSearch_0 = $this->input->get_post('sSearch_0', true); $sSearch_1 = $this->input->get_post('sSearch_1', true); $sSearch_2 = $this->input->get_post('sSearch_2', true); $sSearch_3 = $this->input->get_post('sSearch_3', true); $sSearch_4 = $this->input->get_post('sSearch_4', true); $sEcho = $this->input->get_post('sEcho', true); // Paging if(isset($iDisplayStart) && $iDisplayLength != '-1') { $this->db->limit($this->db->escape_str($iDisplayLength), $this->db->escape_str($iDisplayStart)); } // Ordering if(isset($iSortCol_0)) { for($i=0; $i<intval($iSortingCols); $i++) { $iSortCol = $this->input->get_post('iSortCol_'.$i, true); $bSortable = $this->input->get_post('bSortable_'.intval($iSortCol), true); $sSortDir = $this->input->get_post('sSortDir_'.$i, true); if($bSortable == 'true') { $this->db->order_by($aColumns[intval($this->db->escape_str($iSortCol))], $this->db->escape_str($sSortDir)); } } } /* * Filtering * NOTE this does not match the built-in DataTables filtering which does it * word by word on any field. It's possible to do here, but concerned about efficiency * on very large tables, and MySQL's regex functionality is very limited */ if(isset($sSearch) && !empty($sSearch)) { for($i=0; $i<count($numCols); $i++) { $bSearchable = $this->input->get_post('bSearchable_'.$i, true); // Individual column filtering if(isset($bSearchable) && $bSearchable == 'true') { $this->db->like('member_NID', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_fName', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_mName', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_lName', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_fNameDV', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_mNameDV', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_lNameDV', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_currAtoll', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_currIsland', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_currAddress', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_permAtoll', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_permIsland', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_permAddress', $this->db->escape_like_str($sSearch)); $this->db->or_like('member_status', $this->db->escape_like_str($sSearch)); } } } if(isset($sSearch_0) && !empty($sSearch_0)) { $bSearchable = $this->input->get_post('bSearchable_0', true); // Individual column filtering if(isset($bSearchable) && $bSearchable == 'true') { $this->db->or_like('member_NID', $this->db->escape_like_str($sSearch_0)); } } if(isset($sSearch_1) && !empty($sSearch_1)) { $bSearchable = $this->input->get_post('bSearchable_1', true); // Individual column filtering if(isset($bSearchable) && $bSearchable == 'true') { $this->db->like('member_fName', $this->db->escape_like_str($sSearch_1)); $this->db->or_like('member_mName', $this->db->escape_like_str($sSearch_1)); $this->db->or_like('member_lName', $this->db->escape_like_str($sSearch_1)); } } if(isset($sSearch_2) && !empty($sSearch_2)) { $bSearchable = $this->input->get_post('bSearchable_2', true); // Individual column filtering if(isset($bSearchable) && $bSearchable == 'true') { $this->db->like('member_fNameDV', $this->db->escape_like_str($sSearch_2)); $this->db->or_like('member_mNameDV', $this->db->escape_like_str($sSearch_2)); $this->db->or_like('member_lNameDV', $this->db->escape_like_str($sSearch_2)); } } if(isset($sSearch_3) && !empty($sSearch_3)) { $bSearchable = $this->input->get_post('bSearchable_3', true); // Individual column filtering if(isset($bSearchable) && $bSearchable == 'true') { $this->db->like('member_currAtoll', $this->db->escape_like_str($sSearch_3)); $this->db->or_like('member_currIsland', $this->db->escape_like_str($sSearch_3)); $this->db->or_like('member_currAddress', $this->db->escape_like_str($sSearch_3)); } } if(isset($sSearch_4) && !empty($sSearch_4)) { $bSearchable = $this->input->get_post('bSearchable_4', true); // Individual column filtering if(isset($bSearchable) && $bSearchable == 'true') { $this->db->like('member_permAtoll', $this->db->escape_like_str($sSearch_4)); $this->db->or_like('member_permIsland', $this->db->escape_like_str($sSearch_4)); $this->db->or_like('member_permAddress', $this->db->escape_like_str($sSearch_4)); } } if(isset($sSearch_5) && !empty($sSearch_5)) { $var = 0; $bSearchable = $this->input->get_post('bSearchable_5', true); // Individual column filtering if(isset($bSearchable) && $bSearchable == 'true') { switch($sSearch_5){ case "Registered member": $var = 1; break; case "Non-registered member": $var = 2; break; case "Ex-member": $var = 3; break; } $this->db->like('member_status', $this->db->escape_like_str($var)); } } // Select Data $this->db->select('SQL_CALC_FOUND_ROWS '.str_replace(' , ', ' ', implode(', ', $aColumns)), false); $rResult = $this->db->get($sTable); // Data set length after filtering $this->db->select('FOUND_ROWS() AS found_rows'); $iFilteredTotal = $this->db->get()->row()->found_rows; // Total data set length $iTotal = $this->db->count_all($sTable); // Output $output = array( 'sEcho' => intval($sEcho), 'iTotalRecords' => $iTotal, 'iTotalDisplayRecords' => $iFilteredTotal, 'aaData' => array() ); foreach($rResult->result_array() as $aRow) { $row = array(); /* foreach($aColumns as $col) { $row[] = $aRow[$col]; }*/ for($i=0; $i<sizeof($aColumns); $i++){ if($i == 1) $row[] = $aRow[$aColumns[$i]] ." ". $aRow[$aColumns[$i+1]] ." ". $aRow[$aColumns[$i+2]]; else if($i == 4) $row[] = $aRow[$aColumns[$i]] ." ". $aRow[$aColumns[$i+1]] ." ". $aRow[$aColumns[$i+2]]; else if($i == 7) $row[] = $aRow[$aColumns[$i]] .", ". $aRow[$aColumns[$i+1]] .", ". $aRow[$aColumns[$i+2]]; else if($i == 10) $row[] = $aRow[$aColumns[$i]] .", ". $aRow[$aColumns[$i+1]] .", ". $aRow[$aColumns[$i+2]]; else if($i==2 || $i == 3) continue; else if($i ==5 || $i==6) continue; else if($i ==8 || $i==9) continue; else if($i ==11 || $i==12) continue; else if($i==13){ $status = $aRow[$aColumns[$i]]; $var = ''; switch($status){ case "1": $var = "Non-registered member"; break; case "2": $var = "Registered member"; break; case "3": $var = "Ex-member"; break; } $row[] = $var; } else $row[] = $aRow[$aColumns[$i]]; } $output['aaData'][] = $row; } echo json_encode($output); } </code></pre> <p>The ugliness of the above code has much to do with the fact that it was written in one sitting. </p>
Tags
<javascript><php><jquery><codeigniter-2><jquery-datatables>
Title
Exporting filtered DataTable with serverside Processing (CodeIgniter)
singulars
PostAcceptedAnswerId
1. PO
  singulars
  PostTypePostTypeId
  PTAnswer
PostParentId
1. This table or related slice is empty.
PostTypePostTypeId
1. PTQuestion
UserLastEditorUserId
1. USarkate
UserOwnerUserId
1. USarkate
plurals
PostLinksPostIdRelatedPostId
1. This table or related slice is empty.
PostLinksRelatedPostIdPostId
1. This table or related slice is empty.
PostsAcceptedAnswerId
1. This table or related slice is empty.
PostsParentIdCreationDate
1. PO
  singulars
  PostTypePostTypeId
  PTAnswer
VotesPostIdCreationDate
1. This table or related slice is empty.
CommentsPostId
1. COYou may assume that the export functions (to which there've been references made) work fine.
  singulars
  PostPostId
  POExporting filtered DataTable with serverside Processing (CodeIgniter)
  UserUserId
  USarkate

Querying!

Guidance

A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.