PlayerWet February 2016

fulltext search results showing where most repeated words

I inserted in my web browser and it works great, but it shows me messy results, I would like to show me at the top where over the word or words in the search is repeated.

I looked online tutorials but I can not do it and always come messy, do not understand why.

Right now I have it like this:

$sql="SELECT art,tit,tem,cred,info,que,ano,url 
FROM contenido
WHERE MATCH (art,tit,tem,cred,info) 
AGAINST ('" .$busqueda. "' IN BOOLEAN MODE) 
ORDER BY id DESC";

There is not much information on the Internet about refine or optimize searches for Mysql FULLTEXT. See if experts come through here and so we all learn.

How could refine your search? Thank you.

Answers


Ray February 2016

I think the issue is that you're sorting by the id.

The fulltext sorts by the match score it calculates, showing stronger matches first. When you apply ORDER BY id DESC, you loose this sort-by-match ordering.

You can see the actual score in your result set if you want by:

SELECT art,tit,tem,cred,info,que,ano,url,
      MATCH (art,tit,tem,cred,info) 
      AGAINST ('your term' IN BOOLEAN MODE) AS score
FROM contenido
WHERE MATCH (art,tit,tem,cred,info) 
  AGAINST ('your term' IN BOOLEAN MODE) 
   ORDER BY id DESC

BTW: Use prepared statements for the 'your term' portion.

If your search string has spaces but each term matter, you need to treat them as separate pieces. So if it's important to have BOTH "Mercedes" AND "Benz":

  • Don't: AGAINST ('Mercedes Benz' IN BOOLEAN MODE) <--- This means either Mercedes or Benz
  • Do: AGAINST ('+Mercedes +Benz' IN BOOLEAN MODE)

If you want to have anything that must have the first term, but optionally the second term (ranking higher when both found) do:

 AGAINST ('+Mercedes Benz' IN BOOLEAN MODE)

Here's a long list of combinations: https://dev.mysql.com/doc/refman/5.6/en/fulltext-boolean.html

AND dont forget, get rid of the ORDER BY id DESC. I think you're final query should look something like this for "Mercedes Benz"

SELECT art,tit,tem,cred,info,que,ano,url
    FROM contenido
 WHERE MATCH (art,tit,tem,cred,info) 
  AGAINST ('+Mercedes Benz' IN BOOLEAN MODE);

Yep, freetext in MySQL has a lot of quirks, but play around, you'll get the hang of it.

Post Status

Asked in February 2016
Viewed 1,677 times
Voted 14
Answered 1 times

Search




Leave an answer