Can somebody help me out with a 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
User avatar
Luke
The Ninja Space Mod
Posts: 6424
Joined: Fri Aug 05, 2005 1:53 pm
Location: Paradise, CA

Can somebody help me out with a query?

Post 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. :)
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Can somebody help me out with a query?

Post 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 ...
(#10850)
printf
Forum Contributor
Posts: 173
Joined: Wed Jan 12, 2005 5:24 pm

Post 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
Post Reply