MySQL Fulltext query question

[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 "ResourceTy oe" 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 (ResourceDescri ption) AGAINST ('safety')
AS Score
FROM tblmainresource stable
WHERE MATCH (ResourceDescri ption) AGAINST ('safety')
ORDER BY Score DESC

Query2:
SELECT ResourceID, ResourceName, ResourceType, ResourceSubType , Topic,
MATCH (ResourceDescri ption) AGAINST ('safety')
AS Score
FROM tblmainresource stable
WHERE MATCH (ResourceDescri ption) AGAINST ('safety')
AND ResourceType = 1 OR ResourceType = 2
AND Topic = 3 OR Topic = 4
ORDER BY Score DESC

[Jochen Buennagel]

JT wrote:

WHERE MATCH (ResourceDescri ption) 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 (ResourceDescri ption) AGAINST ('safety')
AND (ResourceType = 1 OR ResourceType = 2)
AND (Topic = 3 OR Topic = 4)

Jochen

[JT]

Jochen Buennagel <za*********@bu ennagel.com> wrote in news:bri1lp$7lg $01
$[e-mail]:

JT wrote:

WHERE MATCH (ResourceDescri ption) 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 (ResourceDescri ption) 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!

[Nikolai Chuvakhin]

JT <jo***@adroitde signers.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 "ResourceTy oe" 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 (ResourceDescri ption) AGAINST ('safety') AS Score
FROM tblmainresource stable
WHERE
MATCH (ResourceDescri ption) AGAINST ('safety')
AND (ResourceType = 1 OR ResourceType = 2)
AND (Topic = 3 OR Topic = 4)
ORDER BY Score DESC;

Cheers,
NC