Page 1 of 1

Can somebody help me out with a query?

Posted: Mon Jan 22, 2007 1:24 pm
by Luke
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:

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')
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. :)

Re: Can somebody help me out with a query?

Posted: Mon Jan 22, 2007 3:26 pm
by Christopher
As I reacall it is something like this:

Code: Select all

SELECT *
FROM products, s01_CFM_ProdFields, s01_ProdValues
WHERE products.prod_id = s01_CFM_ProdValues.prod_id
AND MATCH(products.name, products.sku, products.description, s01_CFM_ProdFields.code, s01_CFM_ProdFields.name) 
AGAINST ('$search_term')
You also might want to do a specific JOIN .. ON ...

Posted: Mon Jan 22, 2007 8:48 pm
by printf
As a side note...

Your MATCH() function must contain only columns that contain a full text index or a multi column fulltext index. So if you want to create a true relevance order by a certain column based on the relevance, then you will need to separate your search, so MySQL performs a unique search on each column, not all the columns at one time!

Code: Select all

SELECT  t1.field1, t1.field2, t2.field1, t2.field5 FROM table1 t1, table2 t2
WHERE
MATCH (t1.field1) AGAINST ('$keywords') OR
MATCH (t1.field2) AGAINST ('$keywords') OR
MATCH (t2.field1) AGAINST ('$keywords') OR
MATCH (t2.field5) AGAINST ('$keywords')
pif