Order by number of occurencies found in each row

delfa

Hi again users!

I have another problem.

I´m implementing a small search engine to a news section in my website.

I send this query to get the results:

SELECT * FROM sites WHERE activo = '1' AND MATCH (title, text) AGAINST ('$query')

and I get the results with no problem.

But how do I get them ordered by relevancy, I mean, how do I order by the number of occurencies found in earch row without having to use php or other script?

tekky

mysql> SELECT * FROM articles
-> WHERE MATCH (title,body) AGAINST ('database');
+----+-------------------+------------------------------------------+
| id | title | body |
+----+-------------------+------------------------------------------+
| 5 | MySQL vs. YourSQL | In the following database comparison ... |
| 1 | MySQL Tutorial | DBMS stands for DataBase ... |
+----+-------------------+------------------------------------------+
2 rows in set (0.00 sec)
The MATCH() function performs a natural language search for a string against a text collection (a set of one or more columns included in a FULLTEXT index). The search string is given as the argument to AGAINST(). The search is performed in case-insensitive fashion. For every row in the table, MATCH() returns a relevance value, that is, a similarity measure between the search string and the text in that row in the columns named in the MATCH() list.

When MATCH() is used in a WHERE clause (see example above) the rows returned are automatically sorted with highest relevance first. Relevance values are non-negative floating-point numbers. Zero relevance means no similarity. Relevance is computed based on the number of words in the row, the number of unique words in that row, the total number of words in the collection, and the number of documents (rows) that contain a particular word

from: http://www.mysql.com/doc/en/Fulltext_Search.html

delfa

it sounds obvious!

thanks man!

tekky

Originally posted by delfa
it sounds obvious!

thanks man!

hehe, sometimes, actually 99% of the time... online manuals are your best friend 😉