Help with recusive loop

[AspIsFun]

I have a dababase table with the following setup

| ID | ParentID | Category |

I Create a recordset and then an array using getRows()

' Create recordset
Set rsMenu = Server.CreateObject("ADODB.Recordset")
rsMenu.ActiveConnection = cnn
rsMenu.Source = "SELECT categoryID, ParentID, Category FROM categories ODERBY Category
rsMenu.CursorType = 0
rsMenu.CursorLocation = 2
rsMenu.LockType = 1
rsMenu.Open()

' Create array
Dim menu
menu = rsMenu.GetRows()


Is there a simple way to create an nested unordered list (with any depth) from that menu array?

 

[Abraxus]

Your question isn't entirely clear to me. You've created a multidimensional array using getRows(). I've answered questions on multidimensional arrays before, so I thought it was a good time to write a FAQ:

http://www.tek-tips.com/faqs.cfm?fid=6473

That will show you how to loop through all the data in a multidimensional array. Obviously instead of just displaying it all you could qualify the data in each row to decide how far to indent it, but in that case "unordered" would be the nicest way you could describe the resulting list of seemingly arbitrarily indented values. You could probably construct your SQL statement with a few more items in the ORDER BY clause so that the data is in the proper order, and then loop through the set to display the child items nested under their parents.

Anyway, I hope the FAQ helps, if you tell me a little more about what you're trying to do exactly I can try and offer some more suggestions.

 

[AspIsFun]

Hi thanks for trying to help me.

I'll try to explain a little bit better.

I have a database table that stores categories like this example.

| ID | ParentID | Category |
1 0 Category 1
2 0 Category 2
3 0 Category 3
4 1 Category 1.1
5 1 Category 1.2
6 4 Category 1.1.1
7 0 Category 4
8 6 Category 1.1.1.1

I would like to output this database table of categories as an html unodered list by it's parent/child relationship.



Here is my code that i am play with

' Create multidimensional array from recordset
Dim category_array
category_array = rsMenu.getRows()

' Use a recursive function to walk throug the array
Function createUL(ByVal nodID, ByVal level)

Const categoryID = 0, parentCategoryID = 1, categoryName = 2

For iRowLoop = 0 to UBound(category_array, 2)
If CStr(category_array(categoryID, iRowLoop)) = CStr(nodID) Then


' Output as tree
Response.Write(str_repeat(level, " " & category_array(categoryName, iRowLoop))

' Restart function
createUL category_array(parentCategoryID, iRowLoop), level+1

End If
Next 'iRowLoop

End Function

' Output tree
Response.Write(createUL(0,0))

But i can't figure out how to make a nested unorderd list from it.

Hope you understand better what i am trying to do.

 

[AspIsFun]

Sorry my code...

This row: If CStr(category_array(categoryID, iRowLoop)) = CStr(nodID) Then

Should be: If CStr(category_array(parentCategoryID, iRowLoop)) = CStr(nodID) Then

 

[Sheco]

Rather than flippng thru the array each time you could keep the recordset object around and use the Filter property to do that work for you.

 

[AspIsFun]

How can i use Filter property of the Recordset to create an unodered list then?

My recordset

| ID | ParentID | Category |
1 0 Category 1
2 0 Category 2
3 0 Category 3
4 1 Category 1.1
5 1 Category 1.2
6 4 Category 1.1.1
7 0 Category 4
8 6 Category 1.1.1.1

I looking for a way go get this is output:

<ul>
<li>Category 1
<ul>
<li>Category 1.1
<ul>
<li>Category 1.1.1
<ul>
<li>Category 1.1.1.1 </li>
</ul>
</li>
</ul>
</li>
<li>Category 1.2 </li>
</ul>
</li>
<li>Category 2 </li>
<li>Category 3 </li>
<li>Category 4 </li>
</ul>

 

[Tarwn]

Rather than use the Recordset object (which abuses your memory and resources), you should order the list. The recordset data you have shown us does not correspond to the SQL statement you posted in the original post. However, I can see how the recordset might get out of order once you get past 9 top elements, as the ordering will be alpha-numeric.

One option would be to have a class that builds the tree, reducing your processing to a single loop through the array. You could use something like the treeview class I posted in this thread: thread333-902773 or you could create a much smaller class using similar logic, but built only for use with this particular data set.

Another option would be to let the database do more of the work of getting your data into a more reasonable order. While you may not be willing to change how the data is stored in the database, you might be able to incorporate the logic from the last couple sections of the following article:

http://www.codeproject.com/database/nestedsets.asp

-T