Calculating relevance from a search

[maddog39]

Hello all,

I am building a website and on my search page I want to be able to display the relevance of each search result. I've searched all over the place but still haven't found any information on how to do this.And obviously im building this website in PHP. Any ideas?

Thanks!
-maddog39

[buddylee17]

SELECT * FROM mytable WHERE MATCH(title,content)
AGAINST('+search* ~term' IN BOOLEAN MODE) ORDER BY relevance DESC;

Have a look at this for more information.

[maddog39]

Okay thanks, i'm taking a look at it now.

[maddog39]

Okay I took a look at that page and everything. But I'm a bit confused because its telling you to order by relevance, when thats what I am trying to determine in the first place. Any ideas?

[buddylee17]

SELECT * FROM mytable WHERE MATCH(title,content)
AGAINST('+search* ~term' IN BOOLEAN MODE) ORDER BY relevance DESC;

No you, the person writing the query, are telling MySQL to return the results in order of relevance. The +sign means that all words must contain the word search. The * is a wildcard meaning that searched, searching, and searchable will be returned. If the record also contains the word term it will be returned and will be considered less relevant than those which dont contain it, due to the ~ operator. If you want the word term to increase the relevancy, dont use the ~.
MySQL uses your query as a kind of voting system. How you arrange your query and which operators you use, determines which row gets the most number of votes. The row with the most votes gets returned first.

[maddog39]

Yea i understand all of that except the ORDER BY relevance part, where are you (am I) getting that field. Where's it coming from? Thats where I'm confused.

[scru]

the relevance? It looks as though the query generates it.

[buddylee17]

I found another way to do this.
The table name is Employees . The field name is Role. The query finds all rows in Role that contain an A and the word Sales and returns them in order of relevancy.

SELECT *, MATCH(Role)
AGAINST('+A* *Sales' IN BOOLEAN MODE)AS relevance FROM Employees ORDER BY relevance DESC;

Hopefully you can manipulate this code and get it to work for you.

[maddog39]

Alright thanks alot. Looks like that query did it for me.