adding fulltext query to existing query

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
cardi777
Forum Commoner
Posts: 54
Joined: Sun Mar 29, 2009 4:26 am

adding fulltext query to existing query

Post by cardi777 »

I am struggling to work out the syntax for joining a normal multi table query with single table fulltext. I can't get my head around the syntax :banghead:

This is the existing syntax which works fine:

Code: Select all

SELECT * FROM products,categories,sub_categories 
WHERE products.approved = 1 
AND products.main_category = categories.id 
AND products.sub_category = sub_categories.id 
My efforts to add fulltext are not cutting it. This is the query i am trying to use fo the search:

Code: Select all

MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) 
AGAINST ('keyword') AS score FROM products 
WHERE MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) AGAINST('keyword') 
This is what i came up with, but for (probably obvious) reasons unknown to me, its not working.

Code: Select all

SELECT * FROM products,categories,sub_categories 
WHERE products.approved = 1 
AND products.main_category = categories.id 
AND products.sub_category = sub_categories.id
AND MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) 
AGAINST ('keyword') AS score FROM products 
WHERE MATCH(product_name,code,tag_1,tag_2,tag_3,tag_4,tag_5,details) AGAINST('keyword') 
Any help would be much appreciated!
Post Reply