URGENT: DISPLAYING DYNAMIC SITE MAP

[CFMan]

Hello guys, I'm having this problem and I hope you will help me to get over it.
I have the following database table:

ID Parent_id Title
1 0 Home
2 0 Interactive
3 2 Wallpapers
4 2 Screensavers
5 2 ECards
6 3 JPGs
7 3 BMPs
8 0 Contacts us

WHEN "PARENT_ID" IS EQUAL TO "0" THIS MEANS THAT THE ITEM IS NOT A SUB MENU. The problem is I wanna retreive the field "Title" from that table, and display the result as a site map. The result may look like this:

HOME
INTERACTIVE
-- Wallpapers
---- JPGs
---- BMPs
-- Screensavers
-- ECards
Contact us

NOTE: I need a code to fit any level of sub menus.

I appreciate your co-operation.

 

[r937]

any level of submenu? that's hard

the general solution involves recursion, and only oracle has a feature that can support it

if you don't mind multiple calls to the database, you could do the recursion from your cf code, but it's very ugly

let's say you left join the table to itself three times, so that you are down in the 4th level

grab the data for all rows so far, i.e. down to the 4th level, then do another query that sees if any rows exist with a Parent_id that matches any of the 4th level rows, which means you have to go deeper

now loop over this logic, grabbing 4 levels at a time, building up an "indented string" plus an array consisting of level IDs

in your example,

0 - - Home
0 - - Interactive
0 2 - Wallpapers
0 2 3 JPGs
0 2 3 BMPs
0 2 - Screensavers
0 2 - ECards
0 - - Contact

what you see here is the layout of the result table that you would get from a left join to the 3rd level

the ugly part comes into play when you realize that the array i mentioned is actually your sort key, i.e. all IDs from the top level down, and that if you go back and get further rows at deeper levels, you have to sort all the intermediate result sets together yourself (since they came from separate queries)

i have solved this problem in my own site very easily, just by limiting the depth to two levels (thus only one call to the database), but i admit this was easy to do, because it's my site

see

http://rudy.ca/sitemap.cfm

rudy