ASP/SQL Generated Menu.

[scouse]

Hi,

I can't get my head around how to do this and wondered if someone could point me in the right direction.

I have an SQL database that stores all the products for a website and stores the following information.

PRODUCT_CATEGORY
PRODUCT_SUBCAT
PRODUCT_NAME

PRODUCT_CATEGORY and PRODUCT_NAME will always have values assigned to them and PRODUCT_SUBCAT is optional but if used will always be one of three possible values (paper, polythene, various).

So two typical examples of data would be

PRODUCT_CATEGORY = "Bags"
PRODUCT_SUBCAT = "Paper"
PRODUCT_NAME = "White Plastic Bags"

or

PRODUCT_CATEGORY = "Boxes"
PRODUCT_SUBCAT = ""
PRODUCT_NAME = "Brown cardboard box"

I need to read these into nested lists in order to create a series of drop down menus on my website. The layout will be like this:


<UL>
<LI>BAGS
<UL>
<LI>Paper
<UL>
<LI>White Plastic Bags</LI>
<LI>Another Plastic Bag</LI>
<LI>Another Plastic Bag 2</LI>

</UL>
</LI>
<LI>Polythene
<UL>
<LI>White Plastic Bags</LI>
<LI>Another Plastic Bag</LI>
<LI>Another Plastic Bag 2</LI>

</UL>
</LI>
<LI>Various
<UL>
<LI>White Plastic Bags</LI>
<LI>Another Plastic Bag</LI>
<LI>Another Plastic Bag 2</LI>

</UL>
</LI>
</UL>
</LI>
<LI>Boxes
<UL>
<LI>Brown Cardboard Box</LI>
<LI>Brown Cardboard Box2</LI>
<LI>Brown Cardboard Box3</LI>

</UL>
</LI>


</UL>


I've managed to figure out the code to retrieve all the info from the database and group it into its relevent categories, but I can't get my head around how to get it to populate the nested lists properly. I can post the code I've done to get it to group into its relevent categories if its helpful.

I appreciate any help anyone can give me on this.

Cheers

[scouse]

Hi, with the help of someone I have managed to start to get my head around the problem, but I'm still struggling.


So far I've managed to do this:


<UL>

<%
if strMENRecords = 0 then

Response.write "<LI>No Categories in Database</LI>"

Else


menRS.MoveFirst
prevcat = ""
prevscat = ""
do WHILE NOT menRS.EOF

strWELLA_MAINTITLE=unfixstring(menRS.Fields("WELLA_MAINTITLE"))
strWELLA_CAT1=unfixstring(menRS.Fields("WELLA_CAT1"))
strWELLA_CAT2=unfixstring(menRS.Fields("WELLA_CAT2"))

if (strWELLA_CAT1 <> prevcat) then
if (prevcat <> "") then response.write("</li>")
response.write("<li class='firstlevel'>" & strWELLA_CAT1 &"<UL>")
End If


if (strWELLA_CAT2 <> prevscat) then
if (prevscat <> "") then response.write("</li>")
response.write("<li>" & strWELLA_CAT2)
End If

prevcat = strWELLA_CAT1
prevscat = strWELLA_CAT2

menRS.MoveNext


loop
end if
%>


<UL>



But thats not working properly... it writes the first Category (in this case "bags") then it writes the sub category out but I can't figure out where to put the </li></ul> to bring it out of the sub category. So instead of it being like this

Menu Working (http://www.siesta-media.co.uk/images/menu_working.jpg)

but it looks like this instead

Menu Not Working (http://www.siesta-media.co.uk/images/menu_notworking.jpg)

Any help would be great!

Thanks :)