Search query woes. Need to add an AND condition to default search query.

mislead_matt

This is what I'm trying to change the default search query into. I have added an AND operator at the end of the search query. It doesn't work. It hangs up when trying to search, stalling at "processing".

[code]
$sWhere = "";
if ( $_GET['sSearch'] != "" ){

$sWhere = "WHERE lname LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"fname LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"personal_city LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"personal_state LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' AND type='condriver'";
}
[/code]

How do I get the ajax search query to take my AND statement, along with the LIKE statements in the default query? Everything works dandy until I try and add AND statements to that query.


Here is the plugin details I'm using.

[code]
$(document).ready(function() {
$('#example').dataTable( {
"bProcessing": true,
"bServerSide": true,
"sAjaxSource": "contract-driver-apps-call.php",
"bStateSave": true,
"sPaginationType": "full_numbers",
"aaSorting": [[ 4, "desc" ]],
"bAutoWidth": false,
"aoColumns": [
{ "sWidth": "23%" },
{ "sWidth": "15%", "sClass": "center" },
{ "sWidth": "21%" },
{ "sWidth": "3%", "sClass": "center" },
{ "sWidth": "15%", "sClass": "center" },
{ "sWidth": "2%", "sClass": "center" },
{ "sWidth": "20%", "sClass": "center" },
]
} );
} );
[/code]

Thank you for your time.

allan

HI mislead_matt,

I think you just need to add some parenthesis to your WHERE condition. I've tried to break it down to be a fairly obvious structure to show what I mean below:

[code]
$sWhere = "";
if ( $_GET['sSearch'] != "" )
{
$sWhere = "WHERE ".
"( ".
"lname LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"fname LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"personal_city LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' OR ".
"personal_state LIKE '%".mysql_real_escape_string( $_GET['sSearch'] )."%' ".
") ".
"AND type='condriver'";
}
[/code]
Does that do the trick for you?

Allan

mislead_matt

It does! You added an extra closing paren on line 9 (or 10, however you want to look at it) that needed to be removed, but after deleting that it works perfectly how I need it to. Thanks Allan!

allan

Doh - thanks for spotting that - I'll modify the original incase anyone else wants to do something similar...

Regards,
Allan

badea

Hi to all !

Allan thanks for your great work!

I searched the forum, but I didn't find the answer.
I want to add an "where" statement in mysql select and perfom that "search box" will search only in this select condition.
How can i do this ? I using a server-side script with php.

Thanks.