Searching Issue

vikas415

Hi,

In my data table...the search box is searching text for only the second column not rest of the column.

The code is just like this..

[code]
$(document).ready(function() {
$('#example').dataTable( {
"sScrollY": 200,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "includes/server_processing.php?section=task&mode=<?php echo $refer; ?>&typeofAccess=<?php echo $type_of_access; ?>&iDisplayStart=0&iDisplayLength=10",

"fnRowCallback": function( nRow, aData, iDisplayIndex ) {
$('td:eq(6)', nRow).addClass( 'center' );
return nRow;
},

"fnDrawCallback": function ( oSettings ) {
/* Need to redo the counters if filtered or sorted */
if ( oSettings.bSorted || oSettings.bFiltered )
{
for ( var i=0, iLen=oSettings.aiDisplay.length ; i

allan

Given that you are using server-side processing, the filtering much occur on the server-side. So likely that is where the issue is. Are you using the example PHP file, customised for your database? Perhaps you can print out the SQL query which is being executed and show us what that looks lie, as that will likely have the answer in it.

Allan

vikas415

Hello Allan,

Yes I'm using the example PHP file and I customized it according to my requirement with some minor changes.

Here I'm not giving any Order By value to query...Data tables are working good.

The search filtering is occurring for only TasK Name column.I have other columns also like start date, end date and owner name. When I enter any date and owner value to search box it is not filtering or filtering according to only task name column.

[code]

<?php

$sQuery = "SELECT SQL_CALC_FOUND_ROWS ".str_replace(" , ", " ", implode(", ", $aColumns))."
FROM $sTable
$sWhere
$sOrder
$sLimit ";

$rResult = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());

/* Data set length after filtering */
$sQuery = "SELECT FOUND_ROWS()";
$rResultFilterTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultFilterTotal = mysql_fetch_array($rResultFilterTotal);
$iFilteredTotal = $aResultFilterTotal[0];

/* Total data set length */
$sQuery = "
SELECT COUNT(".$sIndexColumn.")
FROM $sTable
";
$rResultTotal = mysql_query( $sQuery, $gaSql['link'] ) or die(mysql_error());
$aResultTotal = mysql_fetch_array($rResultTotal);
$iTotal = $aResultTotal[0];


/*
* Output
*/
$action='';
$sOutput = '{';
$sOutput .= '"sEcho": '.intval($_GET['sEcho']).', ';
$sOutput .= '"iTotalRecords": '.$iTotal.', ';
$sOutput .= '"iTotalDisplayRecords": '.$iFilteredTotal.', ';
$sOutput .= '"aaData": [ ';
$j=1;
while ( $aRow = mysql_fetch_array( $rResult ) )
{
$sOutput .= "[";
for ( $i=0 ; $ifetchDetail('firstname','contacts','cid ='.$aRow[ $aColumns[$i] ])." ".$user->fetchDetail('lastname','contacts','cid ='.$aRow[ $aColumns[$i] ]);
$sOutput .= '"'.str_replace('"', '\"', $owner).'",';
}
else{
$sOutput .= '"'.str_replace('"', '\"', $aRow[ $aColumns[$i] ]).'",';
}
}

}
$j++;

$action=$gen->actionDetail($_REQUEST['section'],$aRow['tid'],$_REQUEST['typeofAccess'],$_REQUEST['mode']);
$sOutput.='"'.str_replace('"', '\"', $action ).'",';
$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= "],";

}

$sOutput = substr_replace( $sOutput, "", -1 );
$sOutput .= '] }';

echo $sOutput;


?>
[/code]

I print out the query and it giving me all and fair result...but problem is occurring only on search data table end.

I know default search is global but here searching is not global..I desperately need global search.. even I haven't use any fnFilter option to fix the filter for any specific column.

Here is the code:

[code]

$(document).ready(function() {
$('#example').dataTable( {
"sScrollY": 200,
"bJQueryUI": true,
"sPaginationType": "full_numbers",
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "includes/server_processing.php?section=task&mode=<?php echo $refer; ?>&typeofAccess=<?php echo $type_of_access; ?>&iDisplayStart=0&iDisplayLength=10",

"fnRowCallback": function( nRow, aData, iDisplayIndex ) {
$('td:eq(6)', nRow).addClass( 'center' );
return nRow;
},

"fnDrawCallback": function ( oSettings ) {
/* Need to redo the counters if filtered or sorted */
if ( oSettings.bSorted || oSettings.bFiltered )
{
for ( var i=0, iLen=oSettings.aiDisplay.length ; i

allan

Where is the bit of code which is building $sWhere? That would seem to be the bit which is most likely to be suspect.

Also is there a reason you are sending iDisplayStart=0 and iDisplayLength=10 as GET parameters? DataTables will send these automatically and they might interfere with the result (although likely not the search issue). And one more - you said you printed out the SQL statement - can you do so (and post it here) for when you filter?

Allan

vikas415

Hi Allan,

Here is the sql statement...

SELECT SQL_CALC_FOUND_ROWS tid, task_name, start_date, end_date, task_owner, task_progress FROM task


Here right now I'm not using any $sWhere condition but later I will require it.


Also I removed the following parameter iDisplayStart=0 and iDisplayLength=10.


Allan, Suddenly I tested that the other columns like task_owner which is a numeric field in DB and later I convert it into string by some other PHP functions...like this way..

[code]
elseif($aColumns[$i]=='task_owner'){
$owner=$user->fetchDetail('firstname','contacts','cid ='.$aRow[ $aColumns[$i] ])." ".$user->fetchDetail('lastname','contacts','cid ='.$aRow[ $aColumns[$i] ]);
$sOutput .= '"'.str_replace('"', '\"', $owner).'",';
}

[/code]

This is why it is not searching the text for this column??

also start_date and end_date is a Y-m-d field in a DB when I enter the date in Y-m-d format it is searching and filter the text otherwise not...actually I convert it into d-m-y format for display purpose like this...

[code]
elseif($aColumns[$i]=='start_date'){
$sOutput .= '"'.str_replace('"', '\"', date('d-m-Y',strtotime($aRow[$aColumns[$i]]))).'",';
}
elseif($aColumns[$i]=='end_date'){
$sOutput .= '"'.str_replace('"', '\"', date('d-m-Y',strtotime($aRow[$aColumns[$i]]))).'",';
}
elseif($aColumns[$i]=='task_owner'){
$owner=$user->fetchDetail('firstname','contacts','cid ='.$aRow[ $aColumns[$i] ])." ".$user->fetchDetail('lastname','contacts','cid ='.$aRow[ $aColumns[$i] ]);
$sOutput .= '"'.str_replace('"', '\"', $owner).'",';
}
else{
$sOutput .= '"'.str_replace('"', '\"', $aRow[ $aColumns[$i] ]).'",';
}

[/code]



What i need to do?? For date columns I need to change something around the parameter.??


Also Allan.. when i upload the files on the server the processing time is very slow...and some time I get an alert message...what I need to do??


Any help would be appreciate...


Thank You


Vikas

allan

It's getting a little hard to follow... :-). I'd suggest taking it one step at a time and breaking the problem down. The date filtering is likely to be an issue that is caused by a difference for how you are storing information in the table (datetime or something) and how you are filtering it - you might need to convert the filter in the date / time format that the SQL server understands.

However, the issue to solve before that is why the SQL statement is being broken when filtering is used. There is some difference between when filtering is used and when not, I suspect it will be a substr_replace().

Allan

vikas415

Hello Allan,

I sort out most of issues according to your suggestions.

But one problem is still occurring...:(

When I put the where condition in sql query like this:

[code]
SELECT SQL_CALC_FOUND_ROWS tid, task_name, date_format(start_date,"%d-%m-%Y"), date_format(end_date,"%d-%m-%Y"), task_owner, task_progress FROM task where pid=18
[/code]

Data is showing me in the right way in data tables...but when I enter any text inside the search text box ...then searching is not working...when I remove the where condition in sql query then it is working.

But here I really need to put the where condition in sql and search feature also.

Please help and give me your valuable suggestions. I tried to solve this but I'm unable do this.




Thank You


Vikas

vikas415

Hello Allan,

I waiting for your response...

I'm in big trouble...:(


Thanks


Vikas