Jump to content


Photo

Full text boolean search problems


  • Please log in to reply
5 replies to this topic

#1 JoeF

JoeF
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationScotland

Posted 15 September 2006 - 10:38 PM

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:
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

But i get this error returned:
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

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


#2 Ninjakreborn

Ninjakreborn
  • Members
  • PipPipPip
  • Information Technology Specialist
  • 3,922 posts
  • Age:33

Posted 15 September 2006 - 10:53 PM

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
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.

------

Business Website: http://www.infotechnologist.biz

Personal Website: http://www.joyelpuryear.com

Blog Site: http://www.realmofwriting.com
Services: Web development, application development, mobile development, and custom development. All services listed on my website.


#3 JoeF

JoeF
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationScotland

Posted 15 September 2006 - 10:58 PM

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...ry-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.

#4 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 15 September 2006 - 11:09 PM

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:

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

But i get this error returned:
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


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.
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


#5 JoeF

JoeF
  • Members
  • PipPip
  • Member
  • 14 posts
  • LocationScotland

Posted 15 September 2006 - 11:19 PM

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

#6 shoz

shoz
  • Staff Alumni
  • Advanced Member
  • 600 posts

Posted 15 September 2006 - 11:34 PM

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

I should also mention that you should avoid using reserved words as table names.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users