By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
432,188 Members | 844 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 432,188 IT Pros & Developers. It's quick & easy.

MySQL Fulltext query question

P: n/a
JT
I have a MySQL fulltext search form in place and I now want to filter the
results further. I've added a few multiple select boxes on my form
(ResourceType and Topic), and I'm able to build and execute the SQL
queries properly.

The problem is that when I add the criteria for "ResourceTyoe" and
"Topic", it seems to take precedence over the fulltext matching. In The
two query examples below I'm searching on a single keyword "safety".
Query1 will return 1 result (correct), while Query2 will return 5
(incorrect).

I'm new to fulltext searching and I don't think I have the SQL syntax
correct. Where should I be placing the additional criteria in this query?

-JT

Query1:

SELECT ResourceID, ResourceName, ResourceType, ResourceSubType, Topic,
MATCH (ResourceDescription) AGAINST ('safety')
AS Score
FROM tblmainresourcestable
WHERE MATCH (ResourceDescription) AGAINST ('safety')
ORDER BY Score DESC

Query2:
SELECT ResourceID, ResourceName, ResourceType, ResourceSubType, Topic,
MATCH (ResourceDescription) AGAINST ('safety')
AS Score
FROM tblmainresourcestable
WHERE MATCH (ResourceDescription) AGAINST ('safety')
AND ResourceType = 1 OR ResourceType = 2
AND Topic = 3 OR Topic = 4
ORDER BY Score DESC
Jul 17 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
JT wrote:
WHERE MATCH (ResourceDescription) AGAINST ('safety')
AND ResourceType = 1 OR ResourceType = 2
AND Topic = 3 OR Topic = 4


If I'm not mistaken, AND and OR have the same priority, so the condition
is evaluated left to right. Try grouping the ORs together with
parentheses like this:

WHERE MATCH (ResourceDescription) AGAINST ('safety')
AND (ResourceType = 1 OR ResourceType = 2)
AND (Topic = 3 OR Topic = 4)

Jochen

Jul 17 '05 #2

P: n/a
JT
Jochen Buennagel <za*********@buennagel.com> wrote in news:bri1lp$7lg$01
$1@news.t-online.com:
JT wrote:
WHERE MATCH (ResourceDescription) AGAINST ('safety')
AND ResourceType = 1 OR ResourceType = 2
AND Topic = 3 OR Topic = 4
If I'm not mistaken, AND and OR have the same priority, so the

condition is evaluated left to right. Try grouping the ORs together with
parentheses like this:

WHERE MATCH (ResourceDescription) AGAINST ('safety')
AND (ResourceType = 1 OR ResourceType = 2)
AND (Topic = 3 OR Topic = 4)

Jochen


Hey that worked perfectly. I haven't found many examples of fulltext
queries besides the basic ones in the MySQL manual. I've been afraid to
start going off on the wrong tangent, I've already made some huge
mistakes in getting it to work this far ;)

Thanks a million!
Jul 17 '05 #3

P: n/a
JT <jo***@adroitdesigners.com> wrote in message
news:<Xn*************************@68.1.17.6>...

I have a MySQL fulltext search form in place and I now want to filter the
results further. I've added a few multiple select boxes on my form
(ResourceType and Topic), and I'm able to build and execute the SQL
queries properly.

The problem is that when I add the criteria for "ResourceTyoe" and
"Topic", it seems to take precedence over the fulltext matching. In The
two query examples below I'm searching on a single keyword "safety".
Query1 will return 1 result (correct), while Query2 will return 5
(incorrect).


Use parentheses to set precendence of logical operators:

SELECT
ResourceID, ResourceName, ResourceType, ResourceSubType, Topic,
MATCH (ResourceDescription) AGAINST ('safety') AS Score
FROM tblmainresourcestable
WHERE
MATCH (ResourceDescription) AGAINST ('safety')
AND (ResourceType = 1 OR ResourceType = 2)
AND (Topic = 3 OR Topic = 4)
ORDER BY Score DESC;

Cheers,
NC
Jul 17 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.