Results 1 to 3 of 3

Thread: thesaurus

  1. #1
    Join Date
    Jul 2004

    Unanswered: thesaurus


    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'


    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,


  2. #2
    Join Date
    Apr 2002
    Toronto, Canada
    try a self-join

    select t1.stuff , t2.word
      from thesaurus t1
    left outer
      join thesaurus t2
       and t1.word <> t2.word  
     where [search conditions] | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Feb 2004
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts