nested query for menu

[Amber_j]

I have a table that has id, category_parent and category_name. How can I write
a query so that I can get nested [BULLET] as follwing:


<ul id="nav">
[LI] <a href="#" >first Level menu</a>
[BULLET]
[LI]<a href="#" >second Level menu</a></li>
[LI]<a href="#" >second Level menu</a>
[BULLET]
[LI]<a href="#" >third Level menu</a></li>
[/BULLET]

</li>
[/BULLET]
</li>
[/BULLET]


Please help!!
-A

[Adam Cameron]

Post by Amber_j
I have a table that has id, category_parent and category_name. How can I write

Difficult to answer without knowing what DB you're using.

That's a pretty inefficient way of storing hierarchical relationships. Are
you in the position to change you approach here?

[Ken Ford - *ACE*]

Post by Amber_j
I have a table that has id, category_parent and category_name. How can I write
<ul id="nav">
[LI] <a href="#" >first Level menu</a>
[BULLET]
[LI]<a href="#" >second Level menu</a></li>
[LI]<a href="#" >second Level menu</a>
[BULLET]
[LI]<a href="#" >third Level menu</a></li>
[/BULLET]
</li>
[/BULLET]
</li>
[/BULLET]
Please help!!
-A

Here is how I do one:

<cfquery name="rsProducts" datasource="mydatasource">
SELECT p.ProductID, p.ProductName, c.CategoryName
FROM products p
LEFT JOIN categories c
ON p.CategoryID = c.CategoryID
ORDER BY c.CategoryName, p.ProductName
</cfquery>

<ul>
<cfoutput query="rsProducts" group="CategoryName">
<li>#rsProducts.CategoryName#
<ul>
<cfoutput>
<li>#rsProducts.ProductName#</li>
</cfoutput>
</ul>
</li>
</cfoutput>
</ul>