Full text boolean search problems

[JoeF]

Hey there, im implementing a fulltext search. It all works fine until i try to get it to order them by relevance. Ive read the tutorial here on php freaks that says this should work:
[code]SELECT * FROM `index` WHERE MATCH(title,description)
AGAINST('$squery' IN BOOLEAN MODE) AS score FROM `index` WHERE MATCH(title,description)
AGAINST('$squery' IN BOOLEAN MODE) ORDER BY score DESC LIMIT $from, $max_results[/code]

But i get this error returned:
[code]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS score FROM `index` WHERE MATCH(title,description) AGAINST('test' IN BOOLEAN M' at line 2[/code]

Something to do with the 'AS' bit, there isnt a score field in the database, but in the tutorials ive read it doesnt mention that their should be one. I was assuming that the query assigns the relvance score to that variable so you can pull it out later and order them.


Any help would be great, if you need more info just let me know!
Thanks

[Ninjakreborn]

[code]SELECT * FROM `index` WHERE MATCH(title,description)
AGAINST('$squery' IN BOOLEAN MODE) AS score FROM `index` WHERE MATCH(title,description)
AGAINST('$squery' IN BOOLEAN MODE) ORDER BY score DESC LIMIT $from, $max_results[/code]
not familiar with that, why not just use where statements
WHERE something = '$something'
instead of all of that?  order by, desc, those are standard, and limit, but this IN BOOLEAN MODE, I have never heard of it, or AGAINST, what are they.  WHERE MATCH,  MATCH(never heard of that).?

could that be some of the problems.

[JoeF]

because where statements dont give accurate enough results. What im using is a feature called Fulltext boolean search. it is very accurate, and returns relevant results based on complicated algorithms and it allows far more control over the search.

You can read more about it here: http://www.joedolson.com/boolean-query-in-mysql.php

The match() against() parts work fine on their own. Its the AS score onwards that im having some problems with.



Hope someone can help.

Cheers.

[shoz]

[quote author=JoeF link=topic=108246.msg435218#msg435218 date=1158359911]
Hey there, im implementing a fulltext search. It all works fine until i try to get it to order them by relevance. Ive read the tutorial here on php freaks that says this should work:
[code]SELECT * FROM `index` WHERE MATCH(title,description)
AGAINST('$squery' IN BOOLEAN MODE) AS score FROM `index` WHERE MATCH(title,description)
AGAINST('$squery' IN BOOLEAN MODE) ORDER BY score DESC LIMIT $from, $max_results[/code]

But i get this error returned:
[code]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'AS score FROM `index` WHERE MATCH(title,description) AGAINST('test' IN BOOLEAN M' at line 2[/code]
[/quote]

You have two WHERE clauses, when there should be only one. The "AS" part is usually used to create an alias for columns listed in the column list or tables. You're using it in the WHERE clause.

When you use "IN BOOLEAN MODE" you will not get a relevance score, but a 1 or a 0.

This is more likely to be what you want. It uses IN BOOLEAN MODE to find the matches but not to determine the score.
[code]
SELECT
i.*, (MATCH(title, description) AGAINST('$squery')) AS score
FROM
`index` AS i
WHERE
MATCH(title,description) AGAINST('$squery' IN BOOLEAN MODE)
ORDER BY
score
DESC
LIMIT $from, $max_results
[/code]

[JoeF]

thanks shoz, that got rid of that error. Although it is now throwing up another, its saying "Can't find FULLTEXT index matching the column list".

Although on my table in the db, the title and description fields both have fulltext index's on them.


Any ideas?

Thanks

[shoz]

You need to put a multicolumn FULLTEXT index on title and description.
[code]
ALTER TABLE `index` ADD FULLTEXT(title, desctiption);
[/code]

I should also mention that you should avoid using [url=http://dev.mysql.com/doc/refman/4.1/en/reserved-words.html]reserved words[/url] as table names.