Jump to content

Full text boolean search problems


JoeF

Recommended Posts

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
Link to comment
Share on other sites

[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.
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

[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]
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.