Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2002
    Posts
    189

    Unanswered: sort of... recursive join / concatenation?

    I should say that the reason I'm posting this is that I'm currently doing it in the PHP and it's involving rather a lot of SQL calls and I have a feeling that one carefully-sculpted SQL call could cut out a lot of work for me and my server.

    It's a CMS that generates HTML pages into folders, with content run off a MySQL db. The "pages" table has an ID, a parent page ID, and a folder name. Each page gets generated into its own folder (as default.htm), with the full path being generated from the folder names of its successive parent pages, until you get to root (or "0").

    It's basically... well, it IS... a tree menu. And I'm sure I'm reinventing this particular wheel. But I can't actually find any examples so here I am.

    What it needs to do is... well, you probably get the idea. Practically speaking, I've got three levels of navigation: section homepages (parent page ID = 0), content pages, and content sub-pages. Theoretically speaking though, there's no limit.

    So: where I'm up to / what I do know. It's not impressive:

    SELECT CONCAT(p1.folderName, '/', p2.folderName, '/', p3.folderName) AS pagePath
    FROM tblPages AS p1
    INNER JOIN tblPages AS p2 ON p2.id = p1.parent_page_id
    INNER JOIN tblPages AS p3 ON p3.id = p2.parent_page_id
    WHERE p1.id = @whatever_page_id_i'm_generating

    I haven't tried it but I think that's pretty close. Apart from the obvious flaw that if the page in question doesn't have two pages above it, it's not going to work right. And, uhh, the syntax

    I need to loop, and loops in SQL befuddle me.

    Can I do... a sort of...:

    While the parent_page_id of [whatever page I'm currently recursing through] is greater than 0, join tblPages to itself again and add its folderName to the concat string

    Sorry, that was a bit of a long post to get to a - probably? - simple question. I should probably stop drinking coffee now. Thanks for any replies.

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    use LEFT OUTER JOINs instead

    see examples here: Categories and Subcategories
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Jan 2002
    Posts
    189
    Doh I looked at the issue too hard and forgot to look at the problem. Mind, at least I know what the theory is called now

    Thanks.

  4. #4
    Join Date
    Mar 2007
    Location
    636f6d7075746572
    Posts
    770
    The problem you face with your current structure is that you don't know how many levels of depth you are going to i.e. parent of parent of parent of parent would require three left joins, but how do you know you want three left joins? If you add another depth you have to alter your SQL statement to include that extra level. What Rudy provides is perfectly adequate for what you are attempting to do and the example given on the sqllessons website will guide you through that, however I think you should take a look at another article that I came across. All I have to do is locate it now ...

  5. #5
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    aschk, it is probably the nested set article, there's a link for it at the bottom of the sqllessons article

    there's also a nice article on the mysql site too
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

Posting Permissions

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