Hello and welcome to our community! Is this your first visit?
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New to the CF scene
    Join Date
    Jan 2007
    Thanked 0 Times in 0 Posts

    Recursive Hierarchies(Unlimited Categories and Subcategories

    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%>

  2. #2
    Senior Coder
    Join Date
    Nov 2002
    North-East, UK
    Thanked 0 Times in 0 Posts
    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

  3. #3
    New to the CF scene
    Join Date
    Jan 2007
    Thanked 0 Times in 0 Posts
    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.


Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts