Dynamic Menu Using CFMenu

Below is an example of my database structure:
| menuId | menuName | menuUrl | parentId |
| 1 | ParentPage | /ParentPage | 0 |
| 2 | ChildPage | /ParentPage/ChildPage | 1 |
| 3 | ChildChildPage | /ParentPage/ChildPage/ChildChildPage | 2 |

I can write code to output the first level of parents and children in a dropdown menu but I can't get the next 14 levels of child menu items.

I have a feeling there is an easy solution for this. I'm just not seeing it.

Any ideas?

<cfquery datasource="dbmain" name="qParent">
SELECT * FROM test_menu 
WHERE parentId = 0

<cfset parentcount = 0>
<cfset childcount = 0>

<cfmenu name="menu" type="horizontal" fontsize="14" bgcolor="##CCFFFF">

	<cfloop query="qParent">
    <cfmenuitem name="#REPLACE(REPLACE(qParent.menuName," ","","ALL"),",","","ALL")##parentcount#"
    href="#qParent.menuUrl#" display="#qParent.menuName#"> 
    <cfquery datasource="dbmain" name="qChildren">
    SELECT * FROM test_menu 
    WHERE parentId = #qParent.menuId#
      <cfloop query="qChildren">
           <cfmenuitem name="#REPLACE(REPLACE(qChildren.menuName," ","","ALL"),",","","ALL")##childcount#1"
            href="#qChildren.menuUrl#" display="#qChildren.menuName#"/>  
            <cfset childcount++>
    <cfset parentcount++>


Open in new window

Who is Participating?
timbad2021Connect With a Mentor Author Commented:
Found the answer:
timbad2021Author Commented:
Is everyone asleep today?

>> get the next 14 levels

14 nested levels? Then you're asking about a db query not CF. For MS SQL 2005, the answer
probably involves CTE's.  If you ask in the MS SQL 2005 zone, I'm someone there
could answer it.  (Sorry my brain's too fried to formulate a solution.)
Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

timbad2021Author Commented:
Ok, no luck in the MS SQL zone.

How do I do this using the current database structure that I showed an example of in my first post?
Sorry I didn't see your follow up comments.  Glad you found a solution :)

>> Ok, no luck in the MS SQL zone.

With all due respect, the question was lacking in the details department ;-) The more info you give the experts, the better response you'll get.  Next time be sure post your table structure, and sample of the desired results.  It'll help you get a better (and faster) answer next time.

Menu Level 1
---Menu level 1.1
-----Menu level 1.1.1
--------Menu level

timbad2021Author Commented:
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.