I am trying to build a more accurate search feature for my news section on my website. I use to use LIKE on the content area, however, my problem is that when I search for "three search" when in the database it is written as "three word search" it does not return any content.
I was advised to use MATCH, but I am unable to return ANY results with it. My query returns a variety of fields from multiple tables. My search area has more than 1 search block, which I can change if need be, but I need it to return all results irrespective if they have a low score or not.
Here is my SQL:
$sql = "SELECT news.id, news.name, news.date, news_categories.name as catName, news.author, statuses.name as statusName, MATCH(news.name, news.summary, news.content, news.keywords) AGAINST ('".$content."') AS score FROM news, news_categories, statuses WHERE news.name LIKE '%".$name."%' AND MATCH(news.name, news.summary, news.content, news.keywords) AGAINST ('".$content."') AND news_categories.id LIKE '%".$category."%' AND news.status_id LIKE '%".$status."%' AND news.status_id=statuses.id AND news.category_id=news_categories.id";
Fulltext did not quite help here as my requirements was to always get the values. An easier and more effective way for me was to do a str_replace on the variable containing the search term and replace all " " with an %, so that the term would effectively be "%three%word%search%"