Can somebody help me out with a query?
Posted: Mon Jan 22, 2007 1:24 pm
I am trying to do a fulltext search on three tables at once... the first is called "products" and it has these fields:
prod_id
name
sku
description
image
disp_order
active
and then there are two tables for "custom fields" which look like:
s01_CFM_ProdFields - this table stores custom fields that are available
id
code
name
s01_CFM_ProdValues - this table relates custom fields to products...
field_id
prod_id
value
So I need to be able to search the product table as well as certain CFM_ProdFields within the CFM_ProdValues table. I am thinking something like:
but I don't know what to put in the MATCH() portion and how to find fields within ProdValues... I'm just thoroughly confused by the logic. Can somebody help put it into perspective for me? I'm not asking anybody to write the query for me, but possible just point me in the right direction.
Thanks.
prod_id
name
sku
description
image
disp_order
active
and then there are two tables for "custom fields" which look like:
s01_CFM_ProdFields - this table stores custom fields that are available
id
code
name
s01_CFM_ProdValues - this table relates custom fields to products...
field_id
prod_id
value
So I need to be able to search the product table as well as certain CFM_ProdFields within the CFM_ProdValues table. I am thinking something like:
Code: Select all
SELECT *
FROM products, s01_CFM_ProdFields, s01_ProdValues
WHERE products.prod_id = s01_CFM_ProdValues.prod_id
AND MATCH(fields, to, search) AGAINST ('search terms')Thanks.