MySQL Fulltext query question

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

re: MySQL Fulltext query question

JT wrote:[color=blue]
> WHERE MATCH (ResourceDescription) 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 (ResourceDescription) AGAINST ('safety')
AND (ResourceType = 1 OR ResourceType = 2)
AND (Topic = 3 OR Topic = 4)

Jochen

re: MySQL Fulltext query question

Jochen Buennagel <zang.NOSPAM@buennagel.com> wrote in news:bri1lp$7lg$01
$1@news.t-online.com:
[color=blue]
> JT wrote:[color=green]
>> WHERE MATCH (ResourceDescription) 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 (ResourceDescription) 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!

re: MySQL Fulltext query question

JT <johnt@adroitdesigners.com> wrote in message
news:<Xns94516EA3C79A0t6r2i4p9p8s@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 "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).[/color]

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