Grouping problem: navigation sub-menu items do not display under correct main menu items (edited for clarity / brevity)

Eric Bourland
Eric Bourland used Ask the Experts™
on
ColdFusion 8
SQL Server 2005

Hello, Experts,

I have a grouping problem, about which I have been thinking for much of today. Time to ask for help.

I set up a navigation menu with first level and second level menu items.

The main (or first level) navigation menu items display in a row across a page; and that works great!

But, I am trying to get second level or sub-menu items to display, in groups, under their correct first level or main menu items. I've been working on this for a while, following previous examples. Here is how it looks at present:

http://www.coalcountryteam.org/index_test.cfm

The problem is, the sub-menu items do not appear underneath their correct menu items. Hmmm.

Do I need to modify the query, or the CFOUTPUT? Or both?

I append details: my two tables (one table for main menu items; another table for sub menu items, assigned to respective menu items); the QUERY; and the CFOUTPUT that generates list items for the navigation menu.

I am thinking that I have not asked ColdFusion to correctly group sub menu items under their assigned main menu items.

Thanks for taking a look at this. I look forward to figuring this out.

Eric
Details:

* Main menu items are stored in tbl_acct_MainPages:
PageID int PK
PageTitle nvarchar(50)

* Sub menu items are stored in tbl_acct_SubPages:
SubPageID int PK
SubPageTitle nvarchar(50)
PageID int FK

The query that I use is:

<!--- qry_GetPages selects columns from table tbl_acct_MainPages to display main menu items --->
<!--- and columns from tbl_acct_SubPages to display sub menu items --->
<!--- to create a list of ACCT pages and subpages for all-CSS dropdown navigation menu --->

<cfquery datasource="#ds#" name="qry_GetPages">
           SELECT pg.PageID
                  , pg.PageTitle
                  , sub.SubPageID
                  , sub.SubPageTitle
           FROM tbl_acct_MainPages pg
           LEFT JOIN tbl_acct_SubPages sub
           ON sub.SubPageID = pg.PageID
           ORDER BY pg.pageID, pg.PageTitle, sub.SubPageTitle
</cfquery>

And here is how I set up the CFOUTPUT for qry_GetPages. I think this is what I need to edit!

(The navigation menu is contain in an unordered list and each navigation item is a list item.)

<!-- all-CSS navigation menu, with Page Titles as first level navigation; Sub-Page Titles as second level (dropdown) navigation -->
<ul>
<!-- get list of PageTitles to populate navigation menu -->
<cfoutput query="qry_GetPages" group="PageTitle">
    <li>
    <a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.PageTitle#</a>
    

<!-- get SubPageIDs, SubPageTitles for second level dropdown navigation -->
<!-- get SubPageIDs should appear ONLY under correct PageIDs -->
  <cfoutput>
  <cfif SubPageID is not "">
    <ul>
      <li>
           <a href="/index.cfm?SubPageID=#qry_GetPages.SubPageID#">#qry_GetPages.SubPageTitle#</a>
      </li>
    </ul>
  </cfif>
  </cfoutput>
  
  </li>
</cfoutput>

</ul>


The navigation menu should display as below.

Home

Media								<------ main menu item
----->Videos						<------ sub menu item
----->Photos						<------ sub menu item
----->News Releases					<------ sub menu item
----->For Members of the Media		<------ sub menu item

Contact Us								<------ main menu item

About Us
----->Appalachia's Proud History and the ACCT
----->Benefits
----->Partners
----->Awards
----->Team Coordinator
----->Support Office

Core Goals								<------ main menu item
----->Core Goal 1: Build Local Capacity
----->Core Goal 2: Engage Economic Redevelopment
----->Core Goal 3: Promote Environmental Stewardship
----->Core Goal 4: Enhance Outreach and Education
----->Core Goal 5: Promote Professional Development

Our Sites								<------ main menu item
----->Kentucky
----->Maryland
----->Ohio
----->Pennsylvania
----->Tennessee
----->Virginia
----->West Virginia

Our Research Initiatives								<------ main menu item
----->The Basic Engagement Plan
----->Volunteers for Rural Watersheds
----->The Appalachian Regional Reforestation Initiative

Open in new window

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Your structure may be a bit wonky.
Typically it is easiest to build navigation with a parent/child relationship.

HOME       id = 1          parentid = 0
MEDIA       id = 2          parentid = 0
VIDEOS    id = 3          parentid = 2
PHOTOS  id = 4          parentid = 2
etc.

This way all of your navigation is in a single table. All top level nav items of zero for a parentid, all sub level navigation has the id of the top level they belong under as their parentid.

Your query would like a join to itself to group the children to parents....

<cfquery name="t" datasource="#request.dsn#'>

</cfquery>
sorry...hit return

SQL

SELECT n1.navName as parent, n2.navName as child, n1.id, n1.parentid
FROM navigation n1 INNER JOIN navigation n2 ON n1.id = n2.parentid
SORT BY n1.sortOrder, n1.navName

To output your list elements...

<ul>
<cfoutput query="t" group="parent">
   <li>#parent#
     <ul>
         <cfoutput>
         <li>#child#</li>
         </cfoutput>
     </ul>
</li>
</cfoutput>
</ul>
Also, without testing...yours may work if you simply move the subpage <ul> tags outside of the inner cfoutput. That way it will loop and create <li> elements instead of new lists.
Bootstrap 4: Exploring New Features

Learn how to use and navigate the new features included in Bootstrap 4, the most popular HTML, CSS, and JavaScript framework for developing responsive, mobile-first websites.

Author

Commented:
mackaboogie -- thank you! I will try this and get back to you soon. Eric

Author

Commented:
I tried moving the subpage <ul> tags outside of the inner cfoutput -- but the output is the same.

This current CFOUTPUT structure (see below) does loop through all of the sub menu items -- but puts one sub menu item under each menu menu item, rather than creating a list of sub menu items under each main menu item.

I will try your suggestion:

HOME       id = 1          parentid = 0
MEDIA       id = 2          parentid = 0
VIDEOS    id = 3          parentid = 2
PHOTOS  id = 4          parentid = 2

I am working on that and will get back to you soon. Thank you again.

Eric
<ul>
<!-- get list of PageTitles to populate navigation menu -->
<cfoutput query="qry_GetPages" group="PageTitle">
    <li>
    <a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.PageTitle#</a>
    
<!-- get SubPageIDs, SubPageTitles for second level dropdown navigation -->
<!-- SubPageIDs should appear ONLY under correct PageIDs -->
  <cfoutput>
  <cfif SubPageID is not "">
  <ul>
    <li>
    <a href="/index.cfm?SubPageID=#qry_GetPages.SubPageID#">#qry_GetPages.SubPageTitle#</a>
    </li>
    </ul>
  </cfif>
  </cfoutput>
  
  </li>
</cfoutput>

</ul>

Open in new window

Author

Commented:
I made progress. I am getting an error:

Error Executing Database Query.  
[Macromedia][SQLServer JDBC Driver][SQLServer]Incorrect syntax near 'SORT'.  
 
The error occurred in C:\websites\www.coalcountryteam.org\acctheader.cfm: line 6
Called from C:\websites\www.coalcountryteam.org\index_test.cfm: line 1
Called from C:\websites\www.coalcountryteam.org\acctheader.cfm: line 6
Called from C:\websites\www.coalcountryteam.org\index_test.cfm: line 1
 
4 : <!--- query GetPages: this query selects columns from table tbl_acct_MainPages to create a list of ACCT pages for navigation menu --->
5 :
6 : <cfquery datasource="#ds#" name="qry_GetPages">
7 : SELECT n1.PageTitle AS parent, n2.PageTitle AS child, n1.PageID, n1.ParentID
8 : FROM  tbl_acct_navigation n1

And I will explain what I did. I created a new table:  tbl_acct_navigation. I attach a screen capture of  tbl_acct_navigation.

My query is:
<cfquery datasource="#ds#" name="qry_GetPages">
SELECT n1.PageTitle AS parent, n2.PageTitle AS child, n1.PageID, n1.ParentID
FROM  tbl_acct_navigation n1
INNER JOIN  tbl_acct_navigation n2
ON n1.PageID = n2.ParentID
SORT BY n1.sortOrder, n1.PageTitle
</cfquery>

And the CFOUTPUT is:

<ul>
<cfoutput query="qry_GetPages" group="parent">
  <li>#parent#
    <ul>
        <cfoutput>
        <li>#child#</li>
        </cfoutput>
    </ul>
</li>
</cfoutput>
</ul>

I have tried different things to fix the syntax error. What am I missing? Thank you for your help. I think this will be an elegant solution.

Eric
Untitled-1.gif
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
hello Eric,

How are you sir.
After long time.

In your query,

<cfquery datasource="#ds#" name="qry_GetPages">
SELECT n1.PageTitle AS parent, n2.PageTitle AS child, n1.PageID, n1.ParentID
FROM  tbl_acct_navigation n1
INNER JOIN  tbl_acct_navigation n2
ON n1.PageID = n2.ParentID
SORT BY n1.sortOrder, n1.PageTitle
</cfquery>

Its order by; you need to use order by


<cfquery datasource="#ds#" name="qry_GetPages">
SELECT n1.PageTitle AS parent, n2.PageTitle AS child, n1.PageID, n1.ParentID
FROM  tbl_acct_navigation n1
INNER JOIN  tbl_acct_navigation n2
ON n1.PageID = n2.ParentID
ORDER BY n1.sortOrder, n1.PageTitle
</cfquery>

Author

Commented:
Brichsoft,

ORDER BY. Of course. =)

It is good to hear from you.

I think this might work. I am getting a better result.

I am going to work on this more tomorrow ... it is very late here.

Brichsoft and mackaboogie ... thank you very much. I will come back here after I get some sleep. Take care.

Eric
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
Hey Eric,

have a very nice sleep.
And mackaboogie had took time and help u to solve the problem, so credit goes to him...........

Author

Commented:
Hello, mackaboogie and Brichsoft,

I went ahead and tried out the ORDER BY. =) It works very well, with one small problem: the parent menu items that have no child menu items do not appear, as you can see here:

http://www.coalcountryteam.org/index_test.cfm

... the Home and Contact Us parent menu items are missing.

The query is now:

<cfquery datasource="#ds#" name="qry_GetPages">
SELECT n1.PageTitle AS parent, n2.PageTitle AS child, n1.PageID, n1.ParentID
FROM  tbl_acct_navigation n1
INNER JOIN  tbl_acct_navigation n2
ON n1.PageID = n2.ParentID
ORDER BY n1.PageTitle, n2.PageTitle
</cfquery>

and the CFOUTPUT is now:

<ul>
<cfoutput query="qry_GetPages" group="parent">
  <li> <a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.parent#</a>
    <ul>
        <cfoutput>
        <li>
        <a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.child#</a>
        </li>
        </cfoutput>
    </ul>
</li>
</cfoutput>
</ul>

Do we need to edit the query to make the Home and Contact Us parent menu items appear?

Thank you again. This is very helpful and educational.

Eric
Most Valuable Expert 2015

Commented:
(no points please ..)

>> parent menu items that have no child menu items do not appear
>> FROM  tbl_acct_navigation n1
>> INNER JOIN  tbl_acct_navigation n2

Probably because of the INNER join. Try changing it to a LEFT OUTER join.

Author

Commented:
~insomnia~
~keeps working on problem~

=)

_agx_,

Good to hear from you.

I was wondering if the JOIN were the problem.

I changed INNER JOIN to LEFT OUT JOIN. The result as that all parent and child menu items are displayed:

http://www.coalcountryteam.org/index_test.cfm

<cfquery datasource="#ds#" name="qry_GetPages">
SELECT n1.PageTitle AS parent, n2.PageTitle AS child, n1.PageID, n1.ParentID, n1.sortOrder
FROM  tbl_acct_navigation n1
LEFT OUTER JOIN tbl_acct_navigation n2
ON n1.PageID = n2.ParentID
ORDER BY n1.sortOrder, n1.PageTitle, n2.PageTitle
</cfquery>

I got a slightly better result with a RIGHT OUTER JOIN ... but still no Home or Contact Us parent menu items.

I think I see the logic of using the OUTER JOIN ... we want to compare two tables (or, in this case, two aliases of the same table), and find all rows including rows that are not related to other rows -- for example, parent rows that have no child rows.

Hmmm. I am reading in my SQL book and trying different ideas.

Thank you again to mackaboogie and Brichsoft and _agx_.

Eric
Bhavesh ShahLead Analyst
Top Expert 2010

Commented:
Hi Eric,

Generally Home & Contact are not having any child.
They are just static links.

So you should try this

<cfquery datasource="#ds#" name="qry_GetPages">
SELECT n1.PageTitle AS parent, n2.PageTitle AS child, n1.PageID, n1.ParentID, n1.sortOrder
FROM  tbl_acct_navigation n1
INNER JOIN tbl_acct_navigation n2
ON n1.PageID = n2.ParentID
UNION
SELECT n1.PageTitle AS parent, n2.PageTitle AS child, n1.PageID, n1.ParentID, n1.sortOrder
FROM  tbl_acct_navigation n1
WHERE n1.PageID not in (Select n1.ParentID FROM tbl_acct_navigation)
ORDER BY n1.sortOrder, n1.PageTitle, n2.PageTitle
</cfquery>


This should works.
Most Valuable Expert 2015

Commented:
>> The result as that all parent and child menu items are displayed
>> I got a slightly better result with a RIGHT OUTER JOIN ... but still no Home or
>> Contact Us parent menu items.

Hmm.. do you mean the LEFT JOIN didn't work? If you dump both joins, what are the results?
Sorry Eric, I went to sleep on you. _agx_ the problem with the left join is it creates dupes. Since you get a record for each row in the table the cfoutput group by will treat each record as a parent record as well. You will get a top level MEDIA with the VIDEOS, PHOTOS, etc. listed below it, but then it will also list the VIDEOS as a parent with no children.

I looked at an example where I have built this and I thought I had used a single query, but as it turned out I had the same difficulties you are running into. Even the join created a bit of an ugly result because we create an unordered list even though there were no child elements.

In the older code I had built I did a simple query to get all navigation results and I cache that query using cached within...

I then use query of queries (which is extremely fast) to get all parent nav items and then within the loop I query or queries the children elements....


<cfquery datasource="#ds#" name="qry_GetPages" cachedwithin="#CreateTimeSpan(0, 1, 0, 0)#">
SELECT *
FROM  tbl_acct_navigation
</cfquery>

<cfquery name="fetchTopNav" dbtype="query">
	select * from qry_GetPages
	where parentId = 0
	order by sortorder
</cfquery>

<ul id="nav">
<cfoutput query="fetchTopNav">
  <cfquery name="fetchChildren" dbtype="query">
  	SELECT * 
    FROM fetchAllNav
    WHERE parentID = #fetchTopNav.id#
    ORDER BY sortorder asc
  </cfquery>
  <li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.parent#</a>  
  <cfif fetchChildren.recordCount gt 0>
  	<ul>
    	<cfloop query="fetchChildren"><li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.child#</a></li></cfloop>
  	</ul></cfif>
  </li>
</cfoutput>
</ul>

Open in new window

If you are getting dupes use SELECT DISTINCT ....{column names here}...

A union will also get rid of duplicates.

Author

Commented:
Hello friends,

First, thank you for all of these useful ideas. I am considering using the UNION as Brichsoft suggested. First I will work on the query of queries idea that mackaboogie suggests.

I set up these two queries:

<cfquery datasource="#ds#" name="qry_GetPages" cachedwithin="#CreateTimeSpan(0, 1, 0, 0)#">  
SELECT *  
FROM  tbl_acct_navigation  
</cfquery>  
 
<cfquery name="fetchTopNav" dbtype="query">  
        select * from qry_GetPages  
        where parentId = <CFQUERYPARAM CFSQLTYPE="CF_SQL_INTEGER" VALUE="0">
        order by sortorder  
</cfquery>


And then I set up this HTML / CFOUTPUT:

<ul id="nav">  
<cfoutput query="fetchTopNav">  
  <cfquery name="fetchChildren" dbtype="query">  
        SELECT *  
    FROM tbl_acct_navigation
    WHERE parentID = <CFQUERYPARAM CFSQLTYPE="CF_SQL_INTEGER" VALUE="#fetchTopNav.id#">  
    ORDER BY sortorder asc  
  </cfquery>  
  <li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.parent#</a>    
  <cfif fetchChildren.recordCount gt 0>  
        <ul>  
        <cfloop query="fetchChildren"><li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.child#</a></li></cfloop>  
        </ul></cfif>  
  </li>  
</cfoutput>  
</ul>

(I added some CFQUERYPARAM parameters, for security. I also changed table fetchAllNav to table tbl_acct_navigation. That OK?)

I get this error:

Element ID is undefined in FETCHTOPNAV.  
 
The error occurred in C:\websites\www.coalcountryteam.org\acctheader.cfm: line 131
 
129 :         SELECT *  
130 :     FROM tbl_acct_navigation  
131 :     WHERE parentID = <CFQUERYPARAM CFSQLTYPE="CF_SQL_INTEGER" VALUE="#fetchTopNav.id#">  
132 :     ORDER BY sortorder asc  
133 :   </cfquery>  

So, I am looking at query fetchTopNav .... I am also considering what function the element "id" will have in that query fetchTopNav. How should I define element ID in query fetchTopNav?

Thank you again. =)

Eric
sorry, probably should be pageid...

Author

Commented:
PageID ... of course.

Now I get a "Query Of Queries runtime error."

Query Of Queries runtime error.
Table named tbl_acct_navigation was not found in memory. The name is misspelled or the table is not defined.  
 
The error occurred in C:\websites\www.coalcountryteam.org\acctheader.cfm: line 131
Called from C:\websites\www.coalcountryteam.org\index_test.cfm: line 1
Called from C:\websites\www.coalcountryteam.org\acctheader.cfm: line 131
Called from C:\websites\www.coalcountryteam.org\index_test.cfm: line 1
 
129 :         SELECT *  
130 :     FROM tbl_acct_navigation  
131 :     WHERE parentID = <CFQUERYPARAM CFSQLTYPE="CF_SQL_INTEGER" VALUE="#fetchTopNav.PageID#">  
132 :     ORDER BY sortorder asc  
133 :   </cfquery>  
 

So, it looks like ColdFusion does not know that, in qry_GetPages, we request columns from tbl_acct_navigation?


<cfquery datasource="#ds#" name="qry_GetPages" cachedwithin="#CreateTimeSpan(0, 1, 0, 0)#">  
SELECT *  
FROM  tbl_acct_navigation
</cfquery>  
 
<cfquery name="fetchTopNav" dbtype="query">  
        select * from qry_GetPages  
        where parentId = <CFQUERYPARAM CFSQLTYPE="CF_SQL_INTEGER" VALUE="0">
        order by sortorder  
</cfquery>  
in a query of queries, the table names become the name of the query. So if query qry_GetPages pulled date from the table tbl_acct_navigation - say the id column, the query of queries would reference qry_GetPages.id, not tbl_acct_navigation.id
Sorry Eric, I keep giving you errored code. This should be better.

<!--- original query will grab all records --->
<cfquery datasource="#ds#" name="qry_GetPages" cachedwithin="#CreateTimeSpan(0, 1, 0, 0)#">
SELECT *
FROM  tbl_acct_navigation
</cfquery>

<!--- this will grab just the parent records from the previous query --->
<cfquery name="fetchTopNav" dbtype="query">
SELECT *
FROM qry_GetPages
WHERE parentId = 0
ORDER BY sortorder
</cfquery>

<ul id="nav">
<cfoutput query="fetchTopNav">
  <!--- this will grab just the children records for each parent we are looping over --->
  <cfquery name="fetchChildren" dbtype="query">
    SELECT * 
    FROM qry_GetPages
    WHERE parentID = #fetchTopNav.pageid#
    ORDER BY sortorder asc
  </cfquery>
  <li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.parent#</a>  
  <cfif fetchChildren.recordCount gt 0>
        <ul>
        <cfloop query="fetchChildren"><li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.child#</a></li></cfloop>
        </ul></cfif>
  </li>
</cfoutput>
</ul>

Open in new window

Author

Commented:
Silvera21,

Thanks for your note.

OK, so this:

<cfquery datasource="#ds#" name="qry_GetPages" cachedwithin="#CreateTimeSpan(0, 1, 0, 0)#">  
SELECT *  
FROM  tbl_acct_navigation  
</cfquery>  
 
should be:

<cfquery datasource="#ds#" name="qry_GetPages" cachedwithin="#CreateTimeSpan(0, 1, 0, 0)#">  
SELECT *  
FROM  qry_GetPages    
</cfquery>  

... though that does not make sense, since the query would be querying itself.

Hm.

I've gone with this code:

<cfquery datasource="#ds#" name="qry_GetPages" cachedwithin="#CreateTimeSpan(0, 1, 0, 0)#">  
SELECT PageID, PageTitle, ParentID, SortOrder
FROM  tbl_acct_navigation
</cfquery>  
 
<cfquery name="fetchTopNav" dbtype="query">  
        SELECT *
        FROM qry_GetPages  
        WHERE ParentID = <CFQUERYPARAM CFSQLTYPE="CF_SQL_INTEGER" VALUE="0">
        ORDER BY SortOrder  
</cfquery>  

Is this much OK so far? It makes sense to me:

1) In qry_GetPages I select columns from tbl_acct_navigation
2) In query fetchTopNav I select * from the query qry_GetPages

Does this look correct?

Thank you again for your patient advice. =)

Eric
Eric,

Check out my code repost. I believe I fixed the errors. I apologize for not proofing that well before posting.

Author

Commented:
I think we are getting close!

I am still getting this error:

Element PARENT is undefined in QRY_GETPAGES.  
 
 
The error occurred in C:\websites\www.coalcountryteam.org\acctheader.cfm: line 138
 
136 :     ORDER BY sortorder asc  
137 :   </cfquery>  
138 :   <li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.parent#</a>    
139 :   <cfif fetchChildren.recordCount gt 0>  
140 :         <ul>  
 
I wonder if, in query qry_GetPages, do we need to use the keyword AS to define elements parent and child?
I think is should be changed to #pageTitle#

Author

Commented:
I've been thinking about this some more. Pardon my floundering around. Elements parent and child are undefined.

I find that if I replace parent and child with PageTitle, like this:

<ul>  
<cfoutput query="fetchTopNav">  
  <!--- this will grab just the children records for each parent we are looping over --->  
  <cfquery name="fetchChildren" dbtype="query">  
    SELECT *  
    FROM qry_GetPages  
    WHERE parentID = #fetchTopNav.pageid#  
    ORDER BY SortOrder asc  
  </cfquery>  
  <li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.PageTitle#</a>    
  <cfif fetchChildren.recordCount gt 0>  
        <ul>  
        <cfloop query="fetchChildren"><li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.PageTitle#</a></li></cfloop>  
        </ul></cfif>  
  </li>  
</cfoutput>  
</ul>

... then I see the correct number of parent and child navigation items:

http://www.coalcountryteam.org/index_test.cfm

Although all of the parent and child navigation items are "Home" and link to PageID = 0.

But this is useful information because it shows that we have the correct framework. I am looking for a way to populate this framework with the correct parent and child menu links.

Thanks again for your patience and help.

Eric

Author

Commented:
>>>I think is should be changed to #pageTitle#

We cross posted. =) I did that, and the result is:

http://www.coalcountryteam.org/index_test.cfm

<ul id="nav">
<cfoutput query="fetchTopNav">
  <!--- this will grab just the children records for each parent we are looping over --->
  <li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.pageTitle#</a> 
  <cfquery name="fetchChildren" dbtype="query">
    SELECT * 
    FROM qry_GetPages
    WHERE parentID = #fetchTopNav.pageid#
    ORDER BY sortorder asc
  </cfquery>
  <cfif fetchChildren.recordCount gt 0>
        <ul>
        <cfloop query="fetchChildren"><li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.pageTitle#</a></li></cfloop>
        </ul></cfif>
  </li>
</cfoutput>
</ul>

Open in new window

Author

Commented:
Got it. Yes sir -- I tried that. I still find that all menu items are Home -- which is PageID = 1, ParentID = 0, and SortOrder = 1.

I think the CFLOOP query="fetchChildren" does not loop through the child menu items?

My current code is:

<!--- original query will grab all records --->  
<cfquery datasource="#ds#" name="qry_GetPages" cachedwithin="#CreateTimeSpan(0, 1, 0, 0)#">  
SELECT PageID, PageTitle, ParentID, SortOrder
FROM  tbl_acct_navigation  
</cfquery>  
  
<!--- this will grab just the parent records from the previous query --->  
<cfquery name="fetchTopNav" dbtype="query">  
SELECT *  
FROM qry_GetPages  
WHERE ParentID = 0  
ORDER BY SortOrder  
</cfquery>  



<ul id="nav">  
<cfoutput query="fetchTopNav">  
  <!--- this will grab just the children records for each parent we are looping over --->  
  <li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.pageTitle#</a>   
  <cfquery name="fetchChildren" dbtype="query">  
    SELECT *   
    FROM qry_GetPages  
    WHERE parentID = #fetchTopNav.pageid#  
    ORDER BY sortorder asc  
  </cfquery>  
  <cfif fetchChildren.recordCount gt 0>  
        <ul>  
        <cfloop query="fetchChildren"><li><a href="/index.cfm?PageID=#qry_GetPages.PageID#">#qry_GetPages.pageTitle#</a></li></cfloop>  
        </ul></cfif>  
  </li>  
</cfoutput>  
</ul>

Open in new window

Here is the problem...I keep giving you bad code. Not because I want to but because I am working on twelve things at once....

You have two anchor tags displaying, one for the parent one for the child. I scoped the wrong in my example. The upper should be fetchtopNav.pageTitle and the lower fetchCHildren.pageTitle....here is better code, hopefully I will have it right this time...

<ul id="nav">
<cfoutput query="fetchTopNav">
  <!--- this will grab just the children records for each parent we are looping over --->
  <li><a href="/index.cfm?PageID=#fetchTopNav.PageID#">#fetchTopNav.pageTitle#</a> 
  <cfquery name="fetchChildren" dbtype="query">
    SELECT * 
    FROM qry_GetPages
    WHERE parentID = #fetchTopNav.pageid#
    ORDER BY sortorder asc
  </cfquery>
  <cfif fetchChildren.recordCount gt 0>
        <ul>
        <cfloop query="fetchChildren"><li><a href="/index.cfm?PageID=#fetchChildren.PageID#">#fetchChildren.pageTitle#</a></li></cfloop>
        </ul></cfif>
  </li>
</cfoutput>
</ul>

Open in new window

Author

Commented:
>>>>Not because I want to but because I am working on twelve things at once....

=)

I really appreciate your time. I am familiar with doing twelve things at once. This task is not urgent ... please make it a low priority. I do not want to interrupt your other work.

OK, trying this code now....

... and it works beautifully.

http://www.coalcountryteam.org/index_test.cfm

I am going to test this for a little while, and review the code thoroughly to make sure that I understand everything you showed me. I really appreciate your time. I'll post here again and close the question later this evening.

Hope you are great.

Eric
I guess some of the biggest problems were that I kept copying and pasting code.

Since We have three queries, to use variables from query1 you would scope it #query1.variablename#,
from query 2  - #query2.variablename#,
from query 3 - #query3.variablename#

I accidentally had them all reading as if they were query1.variablename which is why even though it was looking up the correct info for each loop, I directed you (incorrectly) to output the same value all over the place...that is why you got the HOME HOME HOME HOME HOME HOME...

It will make the most sense to compare my error versions against the final version and you can see where the problems were. Thanks for your patience!


Author

Commented:
This is working like a charm and I understand the code.

Thanks very much to mackaboogie, brichsoft, silvera21, and _agx_ as always.

Eric B

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial