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]

Re: MySQL Fulltext query question

JT wrote:[color=blue]
> WHERE MATCH (ResourceDescri ption) AGAINST ('safety')
> AND ResourceType = 1 OR ResourceType = 2
> AND Topic = 3 OR Topic = 4[/color]

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]

Re: MySQL Fulltext query question

Jochen Buennagel <zang.NOSPAM@bu ennagel.com> wrote in news:bri1lp$7lg $01
$1@news.t-online.com:
[color=blue]
> JT wrote:[color=green]
>> WHERE MATCH (ResourceDescri ption) AGAINST ('safety')
>> AND ResourceType = 1 OR ResourceType = 2
>> AND Topic = 3 OR Topic = 4[/color]
>
> If I'm not mistaken, AND and OR have the same priority, so the[/color]
condition[color=blue]
> 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
>
>[/color]

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]

Re: MySQL Fulltext query question

JT <johnt@adroitde signers.com> wrote in message
news:<Xns94516E A3C79A0t6r2i4p9 p8s@68.1.17.6>. ..[color=blue]
>
> 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).[/color]

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