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.

use LEFT OUTER JOINs instead

see examples here: Categories and Subcategories

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.

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 ...

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