Search keyword in phrases

[NoobPHP]

My search should accept "" to create phrases? It allows matching of keywords in " " phrases that is.. When i search "Car Engine", all the records with keywords "This is the Car Engine" would be returned. And not "The Car has an engine"

Now the thing is when i do a search on "Car Engine" , he searches in such a manner

Advanced Search Keyword: \"Car engine\"

Even if there is a match no results were displayed.. Anyone know why?

// Build SQL Query for each keyword entered
foreach ($trimmed_arrayKeyword as $trimmKeyword => $word){
$trimmedKeyword = preg_replace('#\s+#',' ',$trimmedKeyword);
if(strtolower($word )){
$trimmed_arrayKeyword[$trimmKeyword] = "/\b(?<!<)($word)(?!>)\b/i";
}//end if
else $trimmed_arrayKeyword[$trimmKeyword] = '/\b('.preg_quote($word,'/').')\b/i';

$queryKeyword = "SELECT * FROM report WHERE AircraftTailNo REGEXP '[[:<:]]{$word}[[:>:]]' OR DefectInfo REGEXP '[[:<:]]{$word}[[:>:]]' OR Rectifications REGEXP '[[:<:]]{$word}[[:>:]]' OR Comments REGEXP '[[:<:]]{$word}[[:>:]]' OR RelatedDoc REGEXP '[[:<:]]{$word}[[:>:]]'";
$numresultsKeyword =mysql_query ($queryKeyword);
$rowKeyword= mysql_fetch_array ($numresultsKeyword);
$row_num_links_mainKeyword =mysql_num_rows ($numresultsKeyword);

do{ 
$adid_array[] = $rowKeyword[ 'ReportID' ];
}
while( $rowKeyword= mysql_fetch_array($numresultsKeyword));
} //end foreach


//delete duplicate record id's from the array. To do this we will use array_unique function
$tmparr = array_unique($adid_array);
$i=0;
$newarr = isSet($_POST['Search']) ? $_REQUEST['Search'] : '';
foreach ($tmparr as $v) {
$newarr  .= "'" . $v . "',";  //turn newarr into a comma separated string good for an in clause in a query
}
$newarr = substr($newarr,0,strlen($newarr)-1);  //remove the trailing comma

// optional search query  

$queryAircraftDisplay = "SELECT * from report where 1";  

if (!empty($_GET['Keyword'])) {
$queryAircraftDisplay  .= " and reportID in ($newarr)";
}

$resultAircraftDisplay = mysql_query($queryAircraftDisplay) or die ("couldn't execute query ".mysql_error()); 


//bold the resutls
while ($row = mysql_fetch_array($resultAircraftDisplay)) { 

$aircraftType     = preg_replace($trimmed_arrayKeyword, '<b>\\1</b>' , $row['1']);       

[nemonoman]

Unless your string is something like

I have motor in my auto, and its nickname is "Car Engine"!
You won't get a match.

Because you are \ escaping your \" , you have made it part of the search string.

Just submit the phrase. Let's say it's the variable "$searchme".

Also can I recommend that simpler is better than perfect? Your SQL can be a lot simpler just adding % wildcard symbols to the searchphrase, thus:

$queryKeyword = "SELECT * FROM report WHERE AircraftTailNo LIKE '%$searchme%' ";

etc.

So if the searchphrase were "Car Engine" then the SQL would translate to:
$queryKeyword = "SELECT * FROM report WHERE AircraftTailNo LIKE '%Car Engine%' ";

Would find:
I have motor in my auto, and its nickname is "Car Engine"!
Car Engines are OK by me.
I used to be a car Engineer.

MySQL is NOT case sensitive for these sorts of searches.

[NoobPHP]

Because you are \ escaping your \" , you have made it part of the search string.

which part of it actually caused the following.. \"Car engine\"

Anyway i did what you proposed..
when i search for "crac" , records with "cracks", "cracked" were returned which is not what i want..

$queryKeyword = "SELECT * FROM report WHERE AircraftTailNo REGEXP '[[:<:]]{$word}[[:>:]]' OR DefectInfo REGEXP '[[:<:]]{$word}[[:>:]]' OR Rectifications REGEXP '[[:<:]]{$word}[[:>:]]' OR Comments REGEXP '[[:<:]]{$word}[[:>:]]' OR RelatedDoc REGEXP '[[:<:]]{$word}[[:>:]]'";

to

$queryKeyword = "SELECT * FROM report WHERE AircraftTailNo LIKE '%$word%' OR DefectInfo LIKE '%$word%' OR Rectifications LIKE '%$word%' OR Comments LIKE '%$word%' OR RelatedDoc LIKE '%$word%'";

[nemonoman]

Golly, I'm not being any help at all today, am I? I'm really very sorry to have troubled you.

[NoobPHP]

by the way.. you know which part of it actually caused the search to be returned in such a way \"Car engine\" when i do a search on "Car Engine"