Dynamic catalog with unlimited category levels?

[Travis Pupkin]

Hey,

I've done a number of product catalogs/galleries with one or two
category levels (Category > Subcategory). The straightforward way to do
this, of course, is to use database fields for Category and Subcategory
and query off of those fields.

I have a client now who is interested in what sounds to me to be an
unnecessarily complex catalog with an as of yet undefined number of
category levels at their disposal.

My initial response would be just to add more levels of subcategories to
the database, but I was wondering if anyone had come up with a system
allowing the dynamic addition of an unlimited number of subcategories to
a catalog, and would be willing to share your methods. Or, if I should
just stick to the obvious plan.

Thanks.

 

[David Morgan]

Hi

You're correct to ask. I think you're looking for 'recursion'.

One table:

CategoryID, ParentCategoryID, CategoryName

Parent categories will have a ParentCategoryID of zero.

So, to get the top level you say, SELECT CategoryID, Category FROM
tblCategories WHERE ParentCategoryID = 0

If you were looking to build up some sort of tree view where all the
categories can be navigated, you would need two queries. One as above, to
get the top level and then another one that returns the whole table without
the top level categories. Use GetRows to put the results in to an array and
then use something like this to draw it out. Paste this lot in to Notepad
to make more legible. (Have another field which tells me if there are any
subcategories/replies, this is taken from some Forum code.)

Post back if you need more, or I could email the whole page.

Regards

David


'--- Start Snippet

<table border="0" cellpadding="0" cellspacing="0">
<tr>
<th colspan="2"><img border="0" src="../images/1px.gif" WIDTH="1"
HEIGHT="1"></th>
</tr><% For i = 0 To iTopics %>
<tr <%=AlternateRow(i)%>>
<td class="Padded" valign="top"><img border="0" src="../images/1px.gif"
WIDTH="1" HEIGHT="2"><br><%
If arrTopics(5, i) Then ' HasReplies %>
<a href="javascript:toggleMessage(<%=arrTopics(0, i)%>)"><img
id="img_<%=arrTopics(0, i)%>" name="img_<%=arrTopics(0, i)%>" border="0"
src="images/Plus.gif" WIDTH="10" HEIGHT="10"></a><%
End If %></td>
<td class="Padded" valign="top"><span class="<%=IsRead(i)%>"
id="spn<%=arrTopics(0, i)%>"><a href="javascript:vM(<%=arrTopics(0,
i)%>)"><%=Server.HTMLEncode(arrTopics(2, i))%></a></span>
<%=FormatDateTime(arrTopics(3, i), vbShortDate) & " " &
FormatDateTime(arrTopics(3, i), vbShortTime) & " " &
Server.HTMLEncode(arrTopics(4, i))%><%
If arrTopics(5, i) Then ' HasReplies %>
<table id="tblReplies_<%=arrTopics(0, i)%>" cellpadding="0"
cellspacing="0" border="0" class="Hidden">
<tr>
<td><%=BuildThreads(arrMessages, arrTopics(0, i), 0)%></td>
</tr>
</table><%
End If %></td>
</tr>
<tr>
<th colspan="2"><img border="0" src="../images/1px.gif" WIDTH="1"
HEIGHT="1"></th>
</tr><% Next %>
</table><% End If %>
</body><SCRIPT LANGUAGE=javascript>
<!--

//-->
</SCRIPT>
</html>
<SCRIPT LANGUAGE=vbscript RUNAT=Server>
Function BuildThreads(vMessages, ByVal vCurrentTopicID, ByVal vDepth)
If Not bHasMessages Then Exit Function
Dim stThreads, i
For i = 0 to iMessages
If vMessages(1, i) = vCurrentTopicID Then
stThreads = stThreads & "<table style=""padding-top: 5px"" border=""0""
cellpadding=""0"" cellspacing=""0""><tr><td valign=""top""><img border=0
src=""../images/1px.gif"" WIDTH=12 HEIGHT=2><br>"
If vMessages(5, i) Then
stThreads = stThreads & "<a href=""javascript:toggleMessage(" &
vMessages(0, i) & ")""><img id=""img_" & vMessages(0, i) & """ name=""img_"
& vMessages(0, i) & """ border=""0"" src=""images/Plus.gif"" WIDTH=""10""
HEIGHT=""10""></a>"
End If
stThreads = stThreads & "</td><td valign=""top""><span id=""spn" &
vMessages(0, i) & """ class=""" & IsRead2(i) & """><a href=""javascript:vM("
& vMessages(0, i) & ")"">" & _
Server.HTMLEncode(vMessages(2, i)) & "</a></span> " & _
FormatDateTime(vMessages(3, i), vbShortDate) & " " &
FormatDateTime(vMessages(3, i), vbShortTime) & " " & _
Server.HTMLEncode(vMessages(4, i))
If vMessages(5, i) Then
stThreads = stThreads & "<table id=""tblReplies_" & vMessages(0, i) &
""" class=""Hidden""><tr><td>" & _
BuildThreads(vMessages, vMessages(0, i), vDepth +1) & _
"</td></tr></table>"
End If
stThreads = stThreads & "</td></tr></table>"
End If
Next
BuildThreads = stThreads
End Function


'--- End Snippet

 

[Larry Bud]

Hey,

I've done a number of product catalogs/galleries with one or two
category levels (Category > Subcategory). The straightforward way to do
this, of course, is to use database fields for Category and Subcategory
and query off of those fields.

I have a client now who is interested in what sounds to me to be an
unnecessarily complex catalog with an as of yet undefined number of
category levels at their disposal.

My initial response would be just to add more levels of subcategories to
the database, but I was wondering if anyone had come up with a system
allowing the dynamic addition of an unlimited number of subcategories to
a catalog, and would be willing to share your methods. Or, if I should
just stick to the obvious plan.

Thanks.

Seems to me you'd have a category table with the following columns

category_id
parent_category_id
description

Then each item in the catalog would point to the category_id

The maintenance for each sub-category would be simple, as one would
pick the parent category when adding a sub category.

 

[Travis Pupkin]

Seems to me you'd have a category table with the following columns

category_id
parent_category_id
description

Then each item in the catalog would point to the category_id

Thanks; I think I wasn't clear in my question. The table for the
categories/subcategories is not so much of a brain-buster for me, but
the method with which I assign items to the cats/subcats is. I've been
trying to thing if there's a better way to do it than having, say, 5
designated fields in the main db table like:

CategoryID
subcat01ID
subcat02ID
subcat03ID
subcat04ID

....limiting it to One parent category and 4 subcategories. This client
doesn't know yet how deep they want their subcats to go for an item, so
I'm trying to think ahead for them.

I was more curious if someone had found a way to design a totally
flexible category system that allows for unlimited assignations of
category levels to an individual item?

I think probably not, but my realm of knowledge is very narrow, and
asking helps me procrastinate programming.

 

[David Morgan]

I think everyone had your answer

ONE TABLE

CategoryID, ParentCategoryID

That's all you need for unlimited levels.

How you extract the hierarchy is the challenge, which is what my recursive
function accomplishes.

 

[Mark Schupp]

Are you saying that the issue is not in the category tables but that you
want to assign a single row in another table (call it "products") to more
than one category (ie: product "book" could be in categories "children",
"science" , and "non-fiction")?