fulltext search across many-to-many...

Hi all,

I'm creating a little search engine for my product catalog.

The catalog contains lots of tables, but the main ones are 3:

• Product
• language
• translation

the product table contains 4000+ records

the translation table contains 4000+ * 3 (languages)= 12.000+ records

the language table contains... 3 records

Here is the relation between those tables:

products		translation		language
prod_id (INT5)	------------------%26gt;	prodid_trans (INT5)
...		languageid_trans (INT5)	%26lt;-----------	id_lang (INT5)
...		translation_trans (TEXT)		name_lang (VARCHAR65)
...		keywords_trans (TEXT)
...other 40+ fields		options_trans (TEXT)

I can manage easily the INSERT/UPDATE/DELETE transactions.

But now I need to create a search page that allow me to search for (example) a specific keyword.

How can I display results in the currently selected language (language is stored in a session variable).

I tried to use some kind of SELECT statement. Here a sample:

SELECT DISTINCT articoli.nome_art, cat_princ_catpri.catpri_it, cat_princ_catpri.catpri_uk, cat_princ_catpri.catpri_es, subcat_sub.subcat_it, subcat_sub.subcat_uk, subcat_sub.subcat_es, categorie3.cat_it, categorie3.cat_uk, categorie3.cat_es, articoli.foto_art
FROM (((((lingue LEFT JOIN trad ON trad.lingua_id_trad=lingue.id_lng) RIGHT JOIN articoli ON articoli.id_art=trad.art_id_trad) LEFT JOIN subcat_sub ON subcat_sub.id_sub=articoli.subcat_id_art) LEFT JOIN categorie3 ON categorie3.id_cat=articoli.cat_id_art) LEFT JOIN cat_princ_catpri ON cat_princ_catpri.id_catpri=articoli.cat_princ_id_art)
WHERE lingue.nome_lng='KTColParam1' AND articoli.pubblico_art=1 AND (MATCH(articoli.nome_art, articoli,.codice_art) AGAINST('%KTColParam2%') OR MATCH(trad.key_trad) AGAINST ('%KTColParam3%'))
GROUP BY articoli.nome_art
ORDER BY articoli.nome_art ASC

If I use the OR operator between the two MATCH statements I get ALWAYS timeout errors.

If I use only one MATCH statement the query works well.

How can I solve this?

I need to search for any word or phrase across those 3 table. It can't be so difficult... but I can't find a way...

Any idea?

TIA

tony

fulltext search across many-to-many...

AND ( (MATCH(articoli.nome_art, articoli,.codice_art) AGAINST('%KTColParam2%') OR MATCH(trad.key_trad) AGAINST ('%KTColParam3%') ))

Try an extra set of brackets, your OR statement is overriding your AND filters.