Show/Hide columns and Individual Column Filtering Issue

Gus

Hi,
I am using Show/Hide columns in conjunction with Individual Column Filtering.
I am having a problem that I am trying to resolve. I have searched the documentation and forums, but I am missing something.

When I remove a column with show/hide columns, Then I do a search, the search is looking at the wrong column.
I see the problem, $_REQUEST['sSearch_'.$i] is still looking at the old(removed) column for its search.

I am doing a fnUpdate after setting my show/hide columns.

Can anyone point me in the right direction for a fix.


Thanks for any help,
Gus

Gus

Thanks for the help anyway, but I figured it out.

Gus

iuliandum

I wish to know if you resolved that problem and how did you made.
Thanks

iuliandum

Hi Gus,

Say me, please, what made you for resolve this problem. I want to use Show/Hide columns with Individual Column Filtering.
Thanks,
iuliandum

Gus

Hi iuliandum,
Sorry it took me so long to get back to you.
I am happy to share my solution with you. It may not be the best solution, but it works good.
I will show you code based on the the examples from datatables.net.
I use server side processing program: server_processing_filter_col.php.

First in datatables initialization I used fnServerData and pushed the search columns data to server processing program:
[code]
oTable = $('#example').dataTable({
"bJQueryUI": true,
"sPaginationType": "full_numbers",
//Search all
"oLanguage": {
"sSearch": "Search all columns:"
},
//Show all detail
"aoColumns": [
{ "sClass": "center", "bSortable": false },
{ "sWidth": '50px' },
null,
null,
{ "sClass": "center" },
{ "sClass": "center" },
null
],
"aaSorting": [[1, 'asc']],
//Server side
"bStateSave":true,
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_process/server_processing_filter_col.php",
"fnServerData": function ( sSource, aoData, fnCallback ) {

/* Add some extra data*/
aoData.push( { "name": "engine", "value": $('#search_engine').val() } );
aoData.push( { "name": "browser", "value": $('#search_browser').val() } );
aoData.push( { "name": "platform", "value": $('#search_platform').val() } );
aoData.push( { "name": "version", "value": $('#search_version').val() } );
aoData.push( { "name": "grade", "value": $('#search_grade').val() } );
$.getJSON( sSource, aoData, function (json) {
fnCallback(json)
} );

},
[/code]


Then in server side program: server_processing_filter_col.php, I went around the way datatables did it and made my own search. server_processing_filter_col.php is on datatables web site as an example.
[code]

/* Commented out this code
for ( $i=0 ; $i<$_GET['iColumns'] ; $i++ )
{
if ( $_GET['sSearch_'.$i] != '' )
{
if ( $sWhere != "" )
{
$sWhere .= " AND ";
}
else
{
$sWhere .= "WHERE ";
}
$sWhere .= fnColumnToField($i) ." LIKE '%".mysql_real_escape_string( $_GET['sSearch_'.$i] )."%'";
}
}
*/
// Added this code
$engine = $_GET['engine'];
$browser = $_GET['browser'];
$platform = $_GET['platform'];
$version = $_GET['version'];
$grade = $_GET['grade'];
$array_cond = array();
if($engine !='undefined' and $engine !='Rendering engine')
$array_cond[]=array("engine",$engine);
if($browser !='undefined' and $browser !='Browser')
$array_cond[]=array("browser",$browser);
if($platform !='undefined' and $platform !='Platform(s)')
$array_cond[]=array("platform",$platform);
if($version !='undefined' and $version !='Versions')
$array_cond[]=array("version",$version);
if($grade !='undefined' and $grade !='Grades')
$array_cond[]=array("grade",$grade);
foreach($array_cond as $cond)
{
if ( $sWhere != "" )
{
$sWhere .= " AND ";
}
else
{
$sWhere .= "WHERE ";
}
$sWhere .= $cond[0] ." LIKE '%".mysql_real_escape_string( $cond[1] )."%'";
}

[/code]


I hope this helps you. Let me know if you need more.

Thanks,
Gus

iuliandum

Congratulations!
and thank's for code example. It's very simple.....Now it's very simple :)

Thank's and All the best for you!
iuliandum

Gus

iuliandum,

Sure, no problem!

Gus

jrivera

I have the same problem except I am not using server side processing for the filtering. Do you have any suggestions on how to correct that situation?

Gus

Hi jrivera,
It is hard for me to tell, can you send an example of your code?
Do you do this code anywhere:
[code]
for ( $i=0 ; $i<$_GET['iColumns'] ; $i++ )
03.
{
04.
if ( $_GET['sSearch_'.$i] != '' )
05.
{
06.
if ( $sWhere != "" )
07.
{
08.
$sWhere .= " AND ";
09.
}
10.
else
11.
{
12.
$sWhere .= "WHERE ";
13.
}
14.
$sWhere .= fnColumnToField($i) ." LIKE '%".mysql_real_escape_string( $_GET['sSearch_'.$i] )."%'";
15.
}
16.
}
[/code]

Gus

Sorry the code copied weird.

I think you can do just about the same thing I did in my server process

Gus

jrivera

I'm not using server side processing; just the standard filtering provided by datatables.

iuliandum

Hi Gus,

In your code, to be full functionall, have to add this:


[code]
var asInitVals = new Array();
var asOrigVals = new Array();

/* Support functions to provide a little bit of 'user friendlyness' to the textboxes in the footer */
$("tfoot input").each( function (i) {
asInitVals[i] = this.value;

asOrigVals[i] = this.value; //add this line in original code
} );

// and in your Show/Hide function


function fnShowHide( iCol ) {

var bVis = oTable.fnSettings().aoColumns[iCol].bVisible;
oTable.fnSetColumnVis( iCol, bVis ? false : true );
//you have to add this
$("tfoot input").each( function (i) {
if (i<(iCol-1))
asInitVals[i] = asOrigVals[i];
else if (i>=(iCol-1))
if (bVis)
asInitVals[i] = asOrigVals[(i+1)];
else
asInitVals[i] = asOrigVals[i];
} );
}


[/code]

Blalien

Can't seem to get this to work.

I have this at the moment:

[code]

var oTable;
var asInitVals = new Array();

$(document).ready(function() {
oTable = $('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "server_processing.php",
"oLanguage": {
"sSearch": "Search all columns:"
},
"fnServerData": function ( sSource, aoData, fnCallback ) {

/* Add some extra data*/
aoData.push( { "name": "engine", "value": $('#search_engine').val() } );
aoData.push( { "name": "browser", "value": $('#search_browser').val() } );
aoData.push( { "name": "platform", "value": $('#search_platform').val() } );
aoData.push( { "name": "version", "value": $('#search_version').val() } );
$.getJSON( sSource, aoData, function (json) {
fnCallback(json)
} );
},
"bStateSave": true,
"sPaginationType": "full_numbers",
"aoColumns": [
/* Engine */ null,
/* Browser */ null,
{ "sType": "html" },
{ "sType": "html" }
]
} );

$("tfoot input").keyup( function () {
/* Filter on the column (the index) of this element */
oTable.fnFilter( this.value, $("tfoot input").index(this) );
} );


/*
* Support functions to provide a little bit of 'user friendlyness' to the textboxes in
* the footer
*/
$("tfoot input").each( function (i) {
asInitVals[i] = 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 = asInitVals[$("tfoot input").index(this)];
}
} );
} );

[/code]

[code]
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE cus_abbreviation LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"cus_name LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"cus_ig LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"cus_active LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%'";
}

// Added this code
$engine = $_GET['engine'];
$browser = $_GET['browser'];
$platform = $_GET['platform'];
$version = $_GET['version'];
$grade = $_GET['grade'];
$array_cond = array();
if($engine !='undefined' and $engine !='Test')
$array_cond[]=array("cus_abbreviation",$engine);
if($browser !='undefined' and $browser !='Browser')
$array_cond[]=array("cus_name",$browser);
if($platform !='undefined' and $platform !='Platform(s)')
$array_cond[]=array("cus_ig",$platform);
if($version !='undefined' and $version !='Versions')
$array_cond[]=array("cus_active",$version);
foreach($array_cond as $cond)
{
if ( $sWhere != "" )
{
$sWhere .= " AND ";
}
else
{
$sWhere .= "WHERE ";
}
$sWhere .= $cond[0] ." LIKE '%".mysql_real_escape_string( $cond[1] )."%'";
}
[/code]