SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)

    How to handle an unknown number of subcategories

    A while ago I read rudy's page on Categories and Subcategories, which was not only a big help with my photo gallery, but also with a few projects at work. But rudy's sample begged a question that I'm just now getting around to asking.

    Consider the following:

    id | cat_name | parent_id


    And:

    Code SQL:
    SELECT    parent.name AS parent
    	, sub1.name AS sub1
    	, sub2.name AS sub2
    FROM categories
    LEFT JOIN categories AS sub1
    	ON sub1.parent_id = categories.id
    LEFT JOIN categories AS sub2
    	AS sub2.parent_id = sub1.id
    WHERE parent.parent_id IS NULL
    ORDER BY parent, sub1, sub3

    This all works really well IF you know how many levels of subcategory you have. But what if this is an application where a user can create subcategories, and you have no idea how many levels deep they'll go?

    Do you programatically limit how deep the user can go? Do you count the number of levels deep they've gone and alter the query to suit? Just curious!
    <cfset myblog = "http://cydewaze.org/">

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    would ask you to read Categories and Subcategories again, please
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Figures I'd miss that ONE paragraph!

    It seems the SQL is quite easy, so I guess handling the actual coding would be the trickier part. I guess it would take a lot of IF statements to see if the categories went any deeper.
    <cfset myblog = "http://cydewaze.org/">

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by cydewaze View Post
    I guess it would take a lot of IF statements to see if the categories went any deeper.
    not really

    for example, if you wrote the query to go 4 levels deep, you could display 3 levels, indented or whatever, and then have a "more..." link, that, when clicked, would call the same page, which would then go 4 levels down from there

    so, like, only 1 IF statement
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937 View Post
    for example, if you wrote the query to go 4 levels deep, you could display 3 levels, indented or whatever, and then have a "more..." link, that, when clicked, would call the same page, which would then go 4 levels down from there
    Well I was thinking more like a case like my photo album, where you drill down from one page to the next. Something like gallery -> sub1 -> sub2 -> pics in one album,. and gallery -> sub1 -> pics in another.

    Right now I have only one possible level of subcategory, so that's easy to code for without doing much thinking (and I probably won't need anything beyond that) but I was looking at some open source galleries, and one of them said "infinite levels of sub-albums" and I sort of wondered how they did it.
    <cfset myblog = "http://cydewaze.org/">

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    perhaps the nested set model (link in the article mentioned above)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard bronze trophy cydewaze's Avatar
    Join Date
    Jan 2006
    Location
    Merry Land, USA
    Posts
    1,096
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I'll take a peek at that.
    <cfset myblog = "http://cydewaze.org/">


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
  •