listing sub-categories...

[maqdk]

Hi,

I have the following table on MSSQL server:


rowid parent_id name
1 0 cat1
2 0 cat2
3 1 subcat1
4 1 subcat2
5 3 subsubcat1

column rowid is unique for each row. parent_id relates each row with its parent row. This way, there will be unlimited sub-categories.

I want to list all categories, and its subcategories in a select box showing:

cat1
-subcat1
--subsubcat1
-subcat2
cat2

and so on... How can i do this using ASP? or, is there a better way to achieve this? having categories and unlimited levels of sub-categories?

Thanks alot.

/Ali

[WoD]

Oooo baby! It's time to get RECURSIVE!

I wrote a function to do exactly what you're asking a while back, it basically called itself recursively for every level of category until it hit a dead end (a category with no children)

I'm not saying this will work, but think along these lines:


<%
Function RecursiveCategories(intCategory,intIndent)
Set oCn = Server.CreateObject("ADODB.Connection")
oCn.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="&Server.MapPath("central.mdb")&";"

Dim strIndent, intIndex
For intIndex = 0 to intIndent
strIndent = strIndent & "- "
Next

Set oRs = oCn.Execute("SELECT * FROM [category] WHERE [row_id]="&intCategory)
If Not oRs.EoF Then
Response.Write "<option>"&strIndent&oRs("name")&"</option>"
End If
Set oRs = Nothing
Set oRs = oCn.Execute("SELECT * FROM [category] WHERE [parent_id]="&intCategory)
Do While Not oRs.EoF
RecursiveCategories oRs("row_id"), intIndent + 1
oRs.MoveNext
Loop
Set oRs = Nothing

oCn.Close
Set oCn = Nothing
End Function
%>
<select>
<%RecursiveCategories 0, 0%>
</select>


Edit: I took the liberty of testing it, corrected a couple of errors, it seems to work fine for me however you might want to modify it to use OptGroups or some other way of providing better drop-down list formatting than a whole-lotta-dashes. Have fun!

Oh. And PS: You can start at any indent level or start the tree at any category simply by changing the numbers passed when you call RecursiveCategories.