hello,

i am implementing a thesaurus function with php and mysql.

herefor we have table 'thesaurus ' in which all words are stored and grouped. every word has a group name. i. e.:

word: 'mercedes' -> group: 'cars'
word: 'bmw' -> group: 'cars'
word: 'ford' -> group: 'cars'

word: 'george bush' -> group 'warlords'
word: 'osama' -> 'group 'warlords'

etc...


if someone is looking for 'mercedes' the search shall not only match records including this word, but also bmw or ford. several columns are searched (i. e. title, date, details).

i could do that with a lot f php commands and a lot of mysql queries. but actually i want to to it only with sql.

can you help me? i am desperating on that. thanks and best,

uek

try a self-join

The main thing you need to think about is the db structure. Get the structure right and the queries are much easier.

Here is what I would do. I wasn't sure what you were linking your words to... for simplicity, we'll assume the keywords are for news articles

Table: keyword (keyword_id,word)
Table: group (group_id, group_name)
Table: keyword_group (group_id, word_id)
Table: article_keyword (article_id, word_id)
Table: article_group (article_id, group_id)
Table: article (article_id, title, date, details)

Then your query would just be
SELECT whatever FROM keyword AS K
INNER JOIN keyword_group as KG ON K.keyword_id = KG.keyword_id
INNER JOIN article_group AS AG ON KG.group_id = AG.group_id
INNER JOIN article AS A ON AG.article_id = A.article_id
WHERE K.word = '" . $search_term . "'"

Basically you need a way to group an article with a group and a group with a word. I didn't put anything in the query to return cases where an article is directly associated with a word in the article_keyword table but thats easy enough to do.
- John