Multiple Word Seach

[phpretard]

I have a site search but for somes reason it is limited to a one word search.

 

Search1: If I search for "Find" or "Two" it works great. 

Search2: If I were to search for "Find Two" It returns nothing yet I know both are in the DB (based on Search1).

 

 

$find="ONEWORD" //the search return results
$find="TWO WORDS" //the search return nothing and I know the info is there.

$find = strtoupper($find); 
$find = strip_tags($find); 
$find = trim ($find);

$data = mysql_query("SELECT * FROM cases WHERE (casetype LIKE '%$find%') OR (caseinfo LIKE '%$find%') ");

while($result = mysql_fetch_array($data)) 
{ 
$id=$result['id'];
echo "<h1>".$result['casetype']."</h1>"; 
echo "<hr />";
$caseinfo=$result['caseinfo'];
$caseinfofull=$result['caseinfo'];
echo "$caseinfofull";
echo  $shortdesc = substr_replace($caseinfo, '', 400, -1) . "..."; 
echo "<br>"; 
} 

[gevans]

is the string 'Find Two' in your database as it is there, or are they seperated;

 

 

Option 1 - "I need to Find Two words"

 

Option 2 - "Two o'clock I Find my glasses"

 

 

Which of the above?

[phpretard]

SCENARIO 1:

"I need to Find Two words" IS IN THE DB TABLES --|casetype|-- AND --|caseinfo|--.

 

 

If I search for "Find" result returned

 

If I search for "Two" result returned

 

Problem:

If I search for  "Find Two" no result.

 

 

SCENARIO 2:

 

"I need to Find" IS IN THE DB TABLE --|casetype|--

"Two words"  IS IN THE DB TABLE --|caseinfo|--

 

If I search for "Find" result returned

 

If I search for "Two" result returned

 

Problem:

If I search for  "Find Two" no result.

 

 

 

 

[phpretard]

I have put this question on 3 forums with no answer.

 

Is the question confusing or the code?

[phpretard]

		$find = strtoupper($find); 
	$find = strip_tags($find); 
	$find = trim ($find); 
	$find = explode(" ", $find);

	//Now we search for our search term, in the field the user specified 
	foreach ($find as $term){
	$data = mysql_query("SELECT * FROM cases WHERE (casetype LIKE '%$term%') OR (caseinfo LIKE '%$term%') ");
	}
	$matchescount=mysql_num_rows($data);
	$find = implode(" ", $find);

[Psycho]

That doesn't seem right. You hav a loop to run queries, but don't do anything with the results -they just get overwritten by the next query. Also, running multiple queries will be inefficient. This will be more compact and will also hadle a couple other problems. 1) If the string has consecutive spaces in the middle this replaces them with just one. 2) Also "cleans" the string for query purposes:

 

//Clean up the string
$find = strip_tags(strtoupper(trim($find))); 
//Remove multiple spaces from within string
$find = preg_replace('/[ ]+/', ' ', $find);

//Create MySQL code
$find = explode(' ', $find);
foreach ($find as $term)
{
    $term = mysql_real_escape_string($term);
    $terms[] = "\n    (casetype LIKE '%$term%') OR (caseinfo LIKE '%$term%')";
}

//Create the query
$query = "SELECT * FROM cases WHERE" . implode(' OR ', $terms);
$result = mysql_query($query) or die(mysql_error());

 

If the search value was "    one  <b>two</b>  three    " the resulting query would look like this:

SELECT * FROM cases WHERE
    (casetype LIKE '%ONE%') OR (caseinfo LIKE '%ONE%') OR 
    (casetype LIKE '%TWO%') OR (caseinfo LIKE '%TWO%') OR 
    (casetype LIKE '%THREE%') OR (caseinfo LIKE '%THREE%')