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