Complete example for Server side, ColReorder, ColVis, TableTools, Column filtering and State Save

kiwy

Hello,
I'm working for now a month to gets this working and I'd like to thank for all the helps on forums and all, the examples and all the devs, so lets make a full example with :
-JQuery 1.10.2
-DataTable 1.9.4
-ColVis 1.0.8 ( 1.1.0-dev should be considered )
-ColReorder 1.1.0-dev
-TableTools 2.1.5
-Column filtering ( not the plugin )
with serverProcessing using mysql, bsaveState in local storage and not cookie activated, scrollY set and a nice refresh table and reset settings button when you goes into trouble with reordering or visibility !
It has been tested on IE8, recent Chrome, Firefox and Chromium. Column reodering dos not work in IE7 and prior so juste disable that for IE7 and it should work.
an important thing to notice is that I almost not develop a piece of code for this to work, it's just a compilation of all the trick I found on the forum and using on of the greatest js library I ever see
I don't know if I can post that amount of code but let's find out !

--> index.html
[code]
<!--DO NOT FORGET TO IMPORT ALL THE LIBRARY AND THE CSS RELATED TO THOSE LIBRARY-->


$(document).ready(function() {
initMainTable();
} );



Example !





Request
Product
Platform
Status





Loading data from server











[/code]


Js function initMainTable()
[code]
initMainTable(){
//R for column reorder
//C for ColVis
//T for table tools
//r for research
//<"#buttons"> to add you reset settings button
//read documentation for the rest ;-)
sDomDef = 'R<"H"<"#buttons">ClfTr>t<"F"ip>';

/***************************************
this part is for activating column filtering
*****************************************/
/*trick to avoid input fields change column width*/
$("tfoot input").width("100%");
/* Add the events etc before DataTables hides a column */
$("tfoot input").keyup(function() {
/* Filter on the column (the index) of this element */
oTable.fnFilter(this.value, oTable.oApi._fnVisibleToColumnIndex(oTable.fnSettings(), $("tfoot input").index(this)));
});

/*
* Support functions to provide a little bit of 'user friendlyness' to the textboxes
*/

$("tfoot input").each(function(i) {
this.initVal = this.value;
});

$("tfoot input").focus(function() {
if (this.className == "search_init") {
this.className = "";
this.value = "";
}
});

$("tfoot input").blur(function(i) {
if (this.value == "") {
this.className = "search_init";
this.value = this.initVal;
}
});
oTable = $('#mainTable').dataTable({
"sDom" : sDomDef,
"sScrollY" : "500px",
"bProcessing" : true,
"bServerSide" : true,
"bJQueryUI" : true,
"bPaginate" : false,
"bSortable" : true,
"bDeferRender" : false,
//the php file is used with aoColumns definition
"sAjaxSource" : "./ressources/request_table.php",
"aoColumns" : [{
//set sName that match column in table set in request_table.php
//mData is set to allow column reorder
//sWidth: is the size of the column
"sName" : "request_id",
"mData" : "request_id",
"sWidth" : "5%"
}, {
"sName" : "product_name",
"mData" : "product_name",
"sWidth" : "15%"
}, {
"sName" : "platform_name",
"mData" : "platform_name",
"sWidth" : "5%"
},{
"sName" : "status",
"mData" : "status",
"sWidth" : "15%"
}
}],
"oTableTools" : {
"sSwfPath" : "./ressources/js/library/swf/copy_csv_xls_pdf.swf"
},
"oColVis": {
//I personnaly don't want to keep filter on hidden column as you save state do not use the table for a while come back and do not understand why the table misses data. I think it's heathier to remove filter before hidding a column.
"fnStateChange":function ( colIndex, visible) {
oTable.fnFilter('',colIndex);
}
"bStateSave" : true,
"fnStateSave" : function(oSettings, oData) {
localStorage.setItem('DataTables_' + window.location.pathname, JSON.stringify(oData));
},
"fnStateLoad" : function(oSettings) {
return JSON.parse(localStorage.getItem('DataTables_' + window.location.pathname));
},
"fnInitComplete" : function(oSettings) {
var j = 0;
//this part is a bit tricky but it's meant to restore filter in the right input when you mixing column reodering and visibility
for (var i = 0; i < oSettings.aoPreSearchCols.length; i++){
if (oSettings.aoColumns[i].bVisible) {
if (oSettings.aoPreSearchCols[i].sSearch.length > 0){
$("tfoot input")[j].value = oSettings.aoPreSearchCols[i].sSearch;
$("tfoot input")[j].className = "";
}
} else {
oSettings.aoPreSearchCols[i].sSearch = "";
j = j - 1;
}

j += 1;
}
}
});
//now we add the button to the header of table container
$('#buttons').html('Refresh tableReset settings');
}
[/code]

JS function refreshTable() and resetTable()
[code]
function refreshTable() {
$('#mainTable').dataTable().fnDraw();
}

function resetTable() {
localStorage.removeItem('DataTables_' + window.location.pathname);
window.location.reload();
}
[/code]

And now the last but not least the php server side file witch is a bit tuned compare to the provided by Allan here http://datatables.net/release-datatables/examples/data_sources/server_side.html
I will juste provide the differences because it would be too long to put and already existing file
request_table.php
[code]
//variable $aColumns initialization is replaced by
$aColumns = explode( ",", $_GET['sColumns']);
//this means the sName you define for each column must match a database column name whitch can be a security issue. if someone know a better way I take it.

//and line 167 $row[] = $aRow[ $aColumns[$i] ]; is replaced by
$row[$aColumns[$i]] = $aRow[ $aColumns[$i] ];
//this one allow you to have a descriptive json instead of array json return and allow you to reorder column
[/code]

I guest that's all.
I hope this will help you in finding an answer to the question you're asking yourself.
Have a nice day.
Kiwy

zorbakan

I have a very similar table settings and now I want to add State Saving but it was hard to find something with all the features together. This come veeery handy! Thanks! I will take a deep look to this

kiwy

I hope this will help you.
don't hesitate to ask or improve my example.