View Full Version : Recursive Hierarchies(Unlimited Categories and Subcategories

01-12-2007, 02:28 AM
I am having difficulty with recursive hierarchies. I would like to have unlimited categories and subcategories displayed in a breadcrumb inside a drop down box for the entire table. My table is tblCategories and it has a ID and Parent ID field in the database. Any help would be greatly appreciated. I am really struggling and don't quite understand the mathematics behind it. Here is the code I came up with, but its not working:

<form name="form1">
<select name="menu1" onChange="MM_jumpMenu('parent',this,0)">
<option value="#">Select One</option>
<option value="index.asp?Action=categoryset&ID=0">Top Level</option>

Set objRSy = GetData("SELECT * FROM tblCategories")
Do While Not objRSy.EOF or intParID = 0
intParIDCon = objRSy("ParentID")
intParID = objRSy("ParentID")
intIDN = objRSy("ID")
strBase = objRSy("Name")
strBreadCrumb = strBase

strBreadCrumb = strBase
Set objRSu = GetData("SELECT * FROM tblCategories WHERE ID =" & intParID)

Do Until objRSu.EOF

strBreadCrumb = objRSu("Name") & " >> " & strBreadCrumb

<option value="index.asp?Action=categoryset&ID=<%=intIDN%>"><%=strBreadCrumb%></option>


intParID = factorial(intParID)


<%function factorial(intParID)
if intParID = 0 then
factorial = 1
exit function
end if

factorial = intParID * factorial(intParID-1)
end function%>

01-12-2007, 03:43 PM
It depends on how your database is setup.

I use a recursive function that takes the id of the category then checks to see if it has any sub categories associated with it.

For this type of system you would require a new table and use joins to extract the data.

Function listSubCat(CatID, Counter)
SubCat__MMColParam = CatID

Set SubCat = Server.CreateObject("ADODB.Recordset")
SubCat.ActiveConnection = MM_MenuSystem_STRING
SubCat.Source = "SELECT Category.CatID, Category.CatName FROM Category INNER JOIN SubCat ON Category.CatID = SubCat.CatID WHERE (((SubCat.SubCatID)= " + Replace(SubCat__MMColParam, "'", "''") + ")) ORDER BY Category.CatName"
SubCat.CursorType = 0
SubCat.CursorLocation = 2
SubCat.LockType = 1

If Not SubCat.EOF Or Not SubCat.BOF Then
While NOT SubCat.EOF
Response.Write Space(2+Counter) & "<ul>" & vbCRLF
Response.Write Space(4+Counter) & "<li>"
Response.Write "<em>"
Response.Write SubCat("CatName") & " (CategoryID " & SubCat("CatID") & ")"
Response.Write "</em>" & vbCRLF

Counter = Counter + 4
Call listSubCat(SubCat("CatID"), Counter)
Call listLink(SubCat("CatID"), Counter)
Counter = Counter - 4

Response.Write Space(4+Counter) & "</li>" & vbCRLF
Response.Write Space(2+Counter) & "</ul>" & vbCRLF

End If ' end Not SubCat.EOF Or NOT SubCat.BOF

Set SubCat = Nothing
End Function


01-12-2007, 04:12 PM
Thank you my friend for taking the time to help. I've googled myself to death looking for an answer to my problem. Here is how my database is setup. It has other fields, but the important ones are as follows:

ID ParentID Name
1 0 Pets
2 1 Cats
3 2 Siamese Cats

I am trying to get this:

Pets >> Cats >> Siamese Cats

I am new to ASP and SQL. I am not very familiar with JOINS.

My output looks good until I get to the third node, then it craps out.

The output looks like this when I try to add Siamese Cats to Cats:

Pets >> Cats
Cats >> Siamese Cats

It should look like this:

Pets >> Cats
Pets >> Cats >> Siamese Cats

If you have the time, could you please comment you code a little more to show me exactly what you are doing and/or show me how I would incorporate this code into my sample to get the drop down select box to work? Thank you. Your time is greatly appreciated.