SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Category and Sub category

    Hey yet again lol.

    I have an image gallery on my site with the Main categories with pearent_cat_id as 0 and everything else points to some other categories id.

    I want to display the categories in a tabbed list that shows where they belong like:

    Cars
    --Ford
    ----Fiesta
    --Pergeot
    ----106
    ----306
    ------Sports Model
    ----206
    --Nissan
    Food
    --Italian

    etc etc you get the drift.

    I have a query that grabs all the main categories but as i dont know the depth of the categories how would i go about obtaining all the child categories as well.

    Im think im on the right minds of using a function of some kind???

    lol, thanks again

    omnibreak

  2. #2
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Normally, you'll have the parent_id of main category set to 0 and that of sub_category set to id of the main category to which the sub category belongs.

    So, to fetch all main categories
    SELECT * FROM categories WHERE parent_id = 0;

    Now, say you want to retrieve all the subcategories that belong to the category with category_id 5:
    SELECT * FROM categories WHERE parent_id = 5;

    See if you can wrap your head around it!

  3. #3
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    actually, the "normal" thing to do is to use NULL for the parent_id of the top-level or root or main categories

    otherwise it's impossible to define the foreign key properly

    you guys are always defining foreign keys, right?

    omni, have a look at Categories and Subcategories

    i understand that you said you don't know the depth of the categories, but how deep can car models possibly go? three levels? four? eleven? as long as it's some maximum number like this, you can use a number of LEFT OUTER JOINs

    don't go recursive unless you really have to
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  4. #4
    SitePoint Enthusiast
    Join Date
    Jun 2007
    Location
    Frankfurt/Germany
    Posts
    66
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    This is what you call nested set. Google will provide valuable information and code samples on this subject.

  5. #5
    SitePoint Wizard bronze trophy Kailash Badu's Avatar
    Join Date
    Nov 2005
    Posts
    2,560
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    oh Yes Rudy, I vaguely remember that you told somewhere else as well that it should be NULL and not 0 for main categories. Just haven't had enough time to try things out and see how 0 could lead to problems.

  6. #6
    SitePoint Enthusiast
    Join Date
    Mar 2006
    Posts
    81
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cheers for your help guys, I knew i could do it "manualy" as it were but was lookinf for a recursive solution. Any reason why you shouldn't go recursive??

    of course i set up forign keys *shifts eyes nervously* :P

    i have a temp solution for now after googleing "nested set". Sitepoint actaully had an article on the subject lol

    SitePoint - Hierarchical Data

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,216
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by omnibreak View Post
    Any reason why you shouldn't go recursive??
    yes, see post #6

    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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