How to search my website more effeciently

[brooksh]

Here is my code, but it misses a lot of searches just by missing a word, or it pulls too many invalid search results. Any ideas how to make it better? Here is what I have:

 

$sql = "select * from questions where (Question LIKE '%$quest_search%' OR Answer LIKE '%$quest_search%') AND (active='yes')";
$result = mysql_query ($sql);
$count = mysql_num_rows( $result );

if($count == "0"){
$sql = "SELECT * FROM questions WHERE MATCH(Question,Answer) AGAINST ('$quest_search' IN BOOLEAN MODE) AND (active='yes')";

}

 

[davidannis]

What if you explode the search terms into separate words, search on all the words, then rank results by how many of the searched words appear in each row. Something like this:

$search_terms=explode(' ', $quest_search);
foreach ($search_terms as $value){
   if ($like!='') $like.='OR ';
   $like.='\'%'.$value.'%\' ';
}
$sql = "select * from questions where (Question LIKE $like OR Answer LIKE $like) AND (active='yes')";
$result = mysql_query ($sql);
$count = mysql_num_rows( $result );

if($count == "0"){
$sql = "SELECT * FROM questions WHERE MATCH(Question,Answer) AGAINST ('$quest_search' IN BOOLEAN MODE) AND (active='yes')";

}

If the number of results is managable, read the rows into an array and count matches.

I also found this to sort approach by total matches in a row. http://stackoverflow.com/questions/5651605/mysql-order-by-number-of-matches

Edited May 8, 2013 by davidannis

[RonnieCosta50]

Before

 

What if you explode the search terms into separate words, search on all the words, then rank results by how many of the searched words appear in each row. Something like this:

$search_terms=explode(' ', $quest_search);

foreach ($search_terms as $value){

   if ($like!='') $like.='OR ';

   $like.='\'%'.$value.'%\' ';

}

$sql = "select * from questions where (Question LIKE $like OR Answer LIKE $like) AND (active='yes')";
$result = mysql_query ($sql);
$count = mysql_num_rows( $result );

if($count == "0"){
$sql = "SELECT * FROM questions WHERE MATCH(Question,Answer) AGAINST ('$quest_search' IN BOOLEAN MODE) AND (active='yes')";

}

I agree on exploding but then if the user inputs something like this, "What do you do if you step on a mine?" then you should create a list of words to take away from the search parameters.  Put that list in a seperate php.  Get rid of words in the string such as the words "the, if, you, do, a, etc".  So now the search becomes "step on mine".  I think the search results would be more effecitve.  Then explode step on mine and put the new string in the variable %$quest_search%

Edited May 8, 2013 by RonnieCosta50