www.webdeveloper.com
Results 1 to 4 of 4

Thread: Need advice on mysql table structure

  1. #1
    Join Date
    Nov 2012
    Posts
    3

    Need advice on mysql table structure

    Hello,

    I think I'm stuck at this problem now and I'm not sure how to proceed.
    Here's the task: a php site with different registered users.
    Every user can create a directory.
    Every directory can be searched by categories, the users create those categories.
    Unlimited number of categories, with up to 2 sub-categories each.

    I'm explaining this a little complicated, but what it is is - for example - the user needs the following directory:

    Location:
    -USA (LA/ NY/ San Francisco, ...)
    - Europe(France/Germany, ...)
    - Asia (China/Russia, ..)
    Type:
    -Health (Doctor, nurse, .. )
    -Education (Teacher, Student, Lecturer)
    Organization:
    ...
    Scale: - so on

    For every category of those they have let�s say an article. Every article can be placed under different categories, of level2 or 3. For example USA->LA && Health && Education->Lecturer.
    We need to search by categories and get the articles as result.

    So far I have:
    1. Table Categories:
    Category_id, user_id, category_label, category_parent
    2. Table articles
    Article_id, user_id, article_title, article_text

    3. Table relations:
    Relation_id, article_id, level1_rel, level2_rel, level3_rel

    So I know this table structure is far from perfect and I would love to know the better way to do that, but at this point the most important thing is to get this done as soon as possible, as I�m very late with the project.

    So if there�s a way just to come up with a query for the search � will be great. Otherwise I�m curious to other ways as well.
    Someone needs to be able to search for an article, let�s say USA, San Francisco AND Health/Doctors or Just Education, or just Asia, or not select a category to see all of that user.
    (They cannot select just teacher, but can select only Education)


    Thank you in advance

  2. #2
    Join Date
    Aug 2004
    Location
    Ankh-Morpork
    Posts
    19,144
    I would just have each entry in the relationship table define only one article-to-category relationship. That article_to_category relationship table would then only need 2 columns: article_id and category_id, with a unique index on the combination of those 2 columns. Then your article-to-category relationships become totally scalable, allowing any number of unique pairings.
    "Please give us a simple answer, so that we don't have to think, because if we think, we might find answers that don't fit the way we want the world to be."
    ~ Terry Pratchett in Nation

    eBookworm.us

  3. #3
    Join Date
    Nov 2012
    Posts
    3
    The problem is that they are related - that's why I used 3 levels for relation, and I'm searching by 2 of them.
    I have a problem constructing the query:

    For example I have:
    Table relations:
    Relation_id, article_id, level1_rel, level2_rel, level3_rel
    1, 1, 17, 19, 24
    2, 1, 18, 25, 28
    3, 2, 17, 19, 22
    4, 2, 18, 25, 26

    Those are 2 articles
    - 1st -> USA/LA and Education/Lecturer
    - 2nd: ->USA/LA and Education/Student

    Now somebody searches for USA/LA and Education/Lecturer
    I have:

    SELECT articles.*, relations.* FROM articles LEFT JOIN relations ON articles.article_id = relations.article_id WHERE articles.user_id = '2' AND articles.article_id IS NOT NULL AND ( category_3 = 22 OR category_3 = 26 ) GROUP BY articles.article_id

    22:LA
    26:Lecturer

    As a result I need only article 1, that has both LA and Lecturer, however I'm getting as result both articles, as they both have category_3 values 22 OR 26. I need the query to say only records that have both 22 and 26, not or. Looking in Google I think I need an INNER JOINT with nested SELECT, but I'm having trouble building that query.

    Thank you

  4. #4
    Join Date
    Nov 2012
    Posts
    3
    Ok, just for reference, I think I figured it out:

    $search_query="";
    if ($cat1>0){
    $search_query.=" AND r.category_2 = ".$cat1;
    }
    if ($subcat1>0){
    $search_query.=" AND r.category_3 = ".$subcat1;
    }
    if ($cat2>0){
    $search_query.=" AND r1.category_2 = ".$cat2;
    }
    if ($subcat2>0){
    $search_query.=" AND r1.category_3 = ".$subcat2;
    }


    $sql="SELECT article.article_id, article.article_title, r.category_1, r.category_2, r.category_3, r1.category_2, r1.category_3
    FROM (article INNER JOIN relation as r ON article.article_id = r.article_id) INNER JOIN relation as r1 ON r1.article_id=r.article_id
    WHERE article.user_id='{$this->user_id}' ".$search_query."
    GROUP BY article.article_id
    ORDER BY article_order_number ASC, article_title DESC
    ";

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Tags for this Thread

Posting Permissions

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



Recent Articles