Mysql Fulltext Search Ordered By Relevance

[X51]

Sure sounds simple lol. I have read about 20 different web sites explaining how to do this and have tried... oh I've lost count how many different ways to write the same query and I still can't get an exact match to display first. Could someone please point out what I am doing wrong. please

 

I have a table that is a book. It has a field in it called 'text' that holds each sentence in a separate row and I want to search that text for matches contained in a cleaned variable ($word) entered by the user in a search box. The search gets me the correct information, but the order of the displayed sentences is not ordered properly. An exact match may be anywhere in the results. I have used the following queries (among others):

 

	$query_row = "SELECT *, MATCH(text) AGAINST ('$word' 
	IN BOOLEAN MODE) AS relevance FROM book WHERE MATCH(text) 
	AGAINST ('$word' IN BOOLEAN MODE) HAVING relevance > 0.2 
	ORDER BY relevance DESC";

	$query_row = "SELECT *, MATCH(text) AGAINST ('$word') as 
	relevance FROM book WHERE MATCH (text) AGAINST('+$word' 
	IN BOOLEAN MODE) HAVING relevance > 0.2 ORDER BY relevance DESC";

	$query_row = "SELECT *, ((MATCH(text) AGAINST ('" . $word . "' 
	IN BOOLEAN MODE) * 10) + (MATCH(text) AGAINST ('$word' 
	IN BOOLEAN MODE) * 1.5)) AS relevance FROM book WHERE 
	(MATCH (text) AGAINST ('" . $word . "' IN BOOLEAN MODE) > 0 
	OR MATCH (text) AGAINST ('$word' IN BOOLEAN MODE) > 0) 
	ORDER BY relevance DESC";

 

The middle one is the one I currently using, the other two display the relevance for EVERY row as 1 (or 11.5 for the last) . The middle one displays the relevance between 6.8 and 2.1 But the thing that drives me crazy is that in that range an exact match of the search terms are buried in the middle with a lower relevance than non-exact matches.

 

The last query was an attempt to assign a higher relevance to an exact match but it isn't working as all results have 11.5 as the relevance.

 

I sure could use some help in this matter.

[ignace]

HAVING only works with a GROUP BY-clause. It can not be used independently.

 

Edit: Could this solve your problem? http://dev.mysql.com/doc/refman//5.5/en/fulltext-search.html#c1502

[X51]

I did it! One of the problems was searches where a keyword is only 3 letters long that causes an exact match to be displayed with a low relevance since it omits the keyword in that case. But even so some searches with longer keywords were still being displayed after exact matches, such as a search for "hunger strike" might display as:

hunger (some words) strike a pose followed by

the workers formed a hunger strike

 

both results have the keywords but I wanted the second one first. So it occurred to me to do an exact search followed by a loose search and after a LOT of trial and error I came up with the following query that does what I wanted, display exact matches first even if a keyword is 3 letters long.

 

$exact_word = '"' . $word . '"';

$query_row = "SELECT *, CASE WHEN MATCH(text) AGAINST('$exact_word' IN BOOLEAN MODE) 
THEN 1 ELSE 0 END AS exact, MATCH (text) AGAINST('$word') AS close FROM book 
WHERE MATCH (text) AGAINST('$word' IN BOOLEAN MODE) order by exact DESC, close DESC";

[fenway]

Yeah, there's a min length for FULLTEXT.