nested query for menu

Hi,

I think Ken Ford's solution will work for 2 levels only.

We need to use recursive function. I made the sampe. It will work for n levels.

I used a static data for this example. I assumed first level menu contain parent id as 0.

My code:

<!---<cfquery name="request.menu" datasource="#dsn#">
  select id, category_parent, category_name from menu menuTable
</cfquery>--->

<cfscript>
  q = QueryNew('id,category_parent,category_name');
  newRow = QueryAddRow(q, 4);
 
  QuerySetCell(q, "id", "1", 1);
  QuerySetCell(q, "category_parent", "0", 1);
  QuerySetCell(q, "category_name", "First Level -1", 1);
 
  QuerySetCell(q, "id", "2", 2);
  QuerySetCell(q, "category_parent", "0", 2);
  QuerySetCell(q, "category_name", "First Level -2", 2);
 
  QuerySetCell(q, "id", "3", 3);
  QuerySetCell(q, "category_parent", "0", 3);
  QuerySetCell(q, "category_name", "First Level -3", 3);
 
  QuerySetCell(q, "id", "4", 4);
  QuerySetCell(q, "category_parent", "2", 4);
  QuerySetCell(q, "category_name", "second Level -1", 4);
 
  request.menu = q;
 
</cfscript>

<cffunction name="getChilds">
  <cfargument name="pId">

  <cfquery name="qChilds" dbtype="query">
      select * from request.menu
      where category_parent = <cfqueryparam value="#arguments.pId#" cfsqltype="cf_sql_bigint">
    </cfquery>
 
  <cfif qChilds.recordCount gt 0>
    <cfoutput query="qChilds">
      <ul>
        <li><a href="##">#category_name#</a></li>
        #getChilds(id)#
      </ul> 
    </cfoutput>
  <cfelse>
    <cfreturn>
  </cfif>
 
</cffunction>

<cfoutput>#getChilds(0)#</cfoutput>

• as follwing:
  >
  >
  > <ul id="nav">
  >
• <a href="#" >first Level menu</a>
  >
  • 
    >
  • <a href="#" >second Level menu</a></li>
    >
  • <a href="#" >second Level menu</a>
    >
    • 
      >
    • <a href="#" >third Level menu</a></li>
      >
    
    >
    > </li>
    >
  
  > </li>
  >