Best Search Algorithm to Accomplish this?

[ds111]

Hello,

 

I'm building a blog post website, and now I'm on the search part of it.

 

I have a search field which I instruct the user to type in "keywords" they want to search for. The words they type in there are then to be searched in the "title" and "content" of each post in the "posts" table.

 

Here's an example query of my current setup:

 

SELECT * FROM (`posts`) WHERE MATCH(title,content) AGAINST('+term1* ' IN BOOLEAN MODE);

 

This works fine if I have one or two words in they keywords. If I, however, copy and paste an entire sentence from an existing blog post, no results are found ???

Here's an example of a query where I just copy+pasted a sentence from a blog post:

 

SELECT * FROM (`posts`) WHERE MATCH(title,content) AGAINST('+Hello* +everyone* +my* +name* +is* +Bob* ' IN BOOLEAN MODE);

 

Yet, a search for just "Bob" returns the proper result. WHY? What's a better way to perform this search?!

[QuickOldCar]

Mysql fulltext is the proper way to do this as you are.

You may want to explode all the words and modify the operators for each type of search you need.

 

http://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

 

EXACT PHRASE wrapped with double quotes

"word"

 

OR just words with spaces

word word

 

AND words with a + means must contain

+word

 

MUST CONTAIN multiple + words would narrow the search down more to contain just those words

+word +word

 

EXCLUDE words with a - would exclude that word

word -word

 

or can do combinations

"word word" +word -word

 

There are more but these above are the most useful

 

You could even do a multiple if/elseif statement and different types of searches.

Here is an example of how I do it.

I do the advanced search for all or each one of my fields, that's why is so many lines of code there.

http://www.phpfreaks.com/forums/index.php?topic=318221.msg1500385#msg1500385

[QuickOldCar]

I figure I would add to this and just show you the exact search query

 

SELECT * FROM (`posts`) WHERE MATCH(title,content) AGAINST('\"$complete_search_term\"' IN BOOLEAN MODE); 

 

But if you just do a simple query, and have them just wrap in quotes for exact phrases will work also.

SELECT * FROM (`posts`) WHERE MATCH(title,content) AGAINST('$complete_search_term' IN BOOLEAN MODE); 

 

What I do on my simple search is explode all the words, add a + before it if there isn't a + or a - already there, but not if the phrase is wrapped in quotes.

 

(exact) with quotes:

http://dynaindex.com/search.php?s=%22watch+free+tv%22&filter=on&page=1

 

(must contain) without quotes:

http://dynaindex.com/search.php?s=watch+free+tv&filter=on&page=1

 

$trimmed_words = '';
foreach($explode_trimmed as $trim_words){
  if(substr($trim_words,0,1) != "-" || substr($trim_words,0,1) != '"'){
  $trim_words = trim($trim_words);
  $trimmed_words .= " +$trim_words";
  } else {
  $trim_words = trim($trim_words);
  $trimmed_words .= " $trim_words";
  }
  }
  $trimmed_words = trim($trimmed_words);
  $trimmed_words = preg_replace('/\s+/', ' ', $trimmed_words);