Now it's time to populate the subcategory fly out menues. We will start by placing another query inside this category loop we have already created to do a search based on the category ID present in each loop. This is where we create the illusion of a forced relationship to each category. What we are going to do now is create a query that searches for secondary categories related products that are related to the current category. To avoid duplicate results we simply use the “DISTINCT “SQL command. Like this…

<cfquery name="getSubCategories"
    datasource="YourDSN"
    username="YourDSNPassword"
    password="YourDSNUsername">
    SELECT  DISTINCT SubCategoriesTable.*
      FROM ProductRelationalTable INNER JOIN
      (PrpductSecondaryCatecories INNER JOIN Product Related Categories  
      ON SecondaryCatecories-ID = Product Related Categories  - ID)
      ON Related Product_ID = Related Secondary Product_ID
     WHERE (((related Cat_ID)=#getCategories. the category ID in this loop #))
</cfquery>

Next, loop through and output the results of this query…

<cfloop query="getSubCategories">
<!--- You will most likly want to direct this link to:  Results.cfm?#getSubCategories.SecondaryCategory_ID# --->
    <li>
       <a href="#Results.cfm? #getSubCategories.SecondaryCategory_ID#">
           #getSubCategories.SecondaryCategory_Name#</a>
    </li>
</cfloop>

What this does is loops through the results of second query to display secondary categories that are associated with products related to the current category and display them as our fly-out menu. This will give us a menu that dynamically lists all the categories and gives us dropdown menus of the associated secondary categories under each category.
When we put it all together it looks like this…

<div id="menuwrapper">
  <ul id="p7menubar">
<!--- Now, we are going to loop through the getCategories query data set --->
   <cfoutput query="getCategories">
<!--- This qury will find a distinct list of secondary categories that are related to products associated with the Category record identified in each loop through the getCategories data set. --->
<cfquery name="getSubCategories" 
    datasource="YourDSN"
    username="YourDSNPassword"
    password="YourDSNUsername">
     SELECT  DISTINCT SubCategoriesTable.*
       FROM ProductRelationalTable INNER JOIN
         (PrpductSecondaryCatecories INNER JOIN Product Related Categories 
          ON SecondaryCatecories-ID = Product Related Categories-ID)
          ON Related Product_ID = Related Secondary Product_ID
      WHERE (((related Cat_ID)=#getCategories. the category ID in this loop #))
</cfquery>
<!--- first we list the Category name in this loop through the getCategories data set. --->
  <li><a class="trigger" href="##">#getCategories.category_Name# - </a>
    <ul>
<!--- Now we are going to loop through the results of the getSubcategories data set and display each result as a link. --->
     <cfloop query="getSubCategories">
<!--- You will most likly want to direct this link to:  Results.cfm?#getSubCategories.SecondaryCategory_ID# --->
        <li>
            <a href="#Results.cfm? #getSubCategories.SecondaryCategory_ID#">
             #getSubCategories.SecondaryCategory_Name#</a>
         </li>
    </cfloop>
</ul>
</li>
</cfoutput>
  </ul>
<br class="clearit">
</div>

As you can see, it only takes little code and a couple of queries to generate a useful, easy to follow, dynamically delivered drill down menu.

The beauty of this is Project Seven uses unordered lists for all their popup menus! Once you have worked through this a couple of times and get a handle on how this technique works, you will be able to dynamically populate any of the Project Seven menus, and other CSS based menus that follow the same format. It also becomes easy to see how you could drill down yet another level by repeating the process and searching on products that are related to both the passed categories and secondary categories. Something to keep in mind before doing this is that if you start doing too many queries per loop, speed will begin to degrade so for more than two levels there are better solutions. If you should decide to give two levels a try, this PVII menu example is really only designed to go one level deep. If you want to have a multi-tiered menu you will need a more robust, fully scripted solution like Pop Menu Magic. If you find the need to drill down three levels or more, using SQL Server or MySQL as your database and using stored procedures to do the heavy lifting would be a better solution. This method will work very well however for the common task of having a category / subcategory drill down menu.

If you are a Cartweaver 3 user and would like to get a copy of this example with the actual queries in place let me know, I’ll be happy to get it to you.