breadcrumb display, multiple catgories recordset loop

[JohnUS1]

I'm using a database combined of several tables, mostly categories and products.
Just added "breadcrumb" style to web pages
i.e. Home > FirstCatgory > SubCategory > Product Name

The code used is below. It works great.

function BreadCrumbs(dbc,startcatid,product)

'' ----- DECLARE LOCAL VARIABLES -----
dim cathead ' final output string
dim rs ' record set object for holding contents of the record
dim sql
dim catname ' name of the current category
dim catid ' which category record is currently being processed
dim i ' general purpose iterator
dim mylink ' the link that is generated for the category

if product=true then ' if we are already on an item page then we:
						' (a) don't want the product name to be a link, and 
						' (b) specify which [b]category[/b] the product is from
sql="SELECT products.cname, categories.categoryid FROM [red]categories[/red] "
sql=sql & "INNER JOIN ([red]products[/red] "
sql=sql & "INNER JOIN [red]prodcategories[/red] ON "
sql=sql & "products.catalogid = prodcategories.intcatalogid) "
sql=sql & "ON categories.categoryid = prodcategories.intcategoryid "
sql=sql & "WHERE products.catalogid=" & startcatid 
sql=sql & " ORDER BY (categories.hassubcategory)"
	set rs=dbc.execute (sql)
	cathead=rs("cname")
	catid=rs("categoryid")
	Closerecordset rs
else
	cathead=""
	catid = startcatid
End if

'' ----- GET ALL CATEGORIES AND BUILD THE RETURN STRING ----- 
Do while catid <> 0
Set rs=dbc.execute("select * from categories where categoryid=" & catid)
If not rs.eof Then
	strsubcat=rs("hassubcategory")
	if isNULL(strsubcat) then strsubcat=""

	catname=rs("catdescription")

	if catid = startcatid then ' case where it's a product name
		mylink=catname		
	else 
		if strsubcat<>"" then ' case where it's a root category
			mylink="<a href='" & siteurl & "topcat.asp?id=" & catid & "&cat=" & Server.URLEncode(catname) & ">" & catname & "</a>"
		else ' case where it's a category with only products in it
			mylink="<a href='" & siteurl & "productspage.asp?id=" & catid & "&cat=" & Server.URLEncode(catname) & ">" & catname & "</a>"
		end if	
	end if	

	If catid = startcatid Then
		
		cathead = mylink

	Else
		cathead = mylink & " &#62; " & cathead 'separator 
	End If
	catid = rs("highercategoryid")
Else
	Closerecordset rs
	Exit Do
End If
Closerecordset rs
Loop 

BreadCrumbs = " <small> <a href='" & mainurl & "'>Directory</a> &#62; " & cathead & " </small> "
end function

3 tables are used for this working setup

products = includes products names and ID's
categories = includes categories names, IDs and relationship (category, parent category, has-subcategory =yes/null)
prodcategories = id (index), catalogid (product id), categoryid (all category id where it fits in).

Problem:
Many items belong in more than only one subcategory.
The current function would only show the first.
I added it ORDER BY (categories.hassubcategory) so it picks the first category that has no subcategory (bottom of list). That's a partial solution only, as it shows only one option.

Question:
How do I develop the syntex to get the list of all categories (where has_subcategories<>"yes") that are included in prodcategories and output their structure via a loop.

Any help would be greatly appreciated

 

[JohnUS1]

Adding info:

We're talking about 3 tables

products = holds products names and ID's

categories = holds categories names, IDs and relationship (category, parent category, has-subcategory =yes/null)

prodcategories = [product / categories]: a 3 row table,
id (for indexing - unique value),
catalogid (product id), - may be [and is] duplicate values of the products id
categoryid - (category id where catalogid would fit into).

example
catalogid 1 fits into category 100,104,7007

prodcategories table
id = 1
catalogid = 1
categoryid = 100

id = 2
catalogid = 2
categoryid = 105

id = 3
catalogid = 1
categoryid = 104

id = 4
catalogid = 1
categoryid = 7007

id = 5
catalogid = 3
categoryid = 15


Again, thanks for any help

 

[Tarwn]

I'm not sure I understand what your trying to achieve. From your question above it sounds like you want to output all of the lowest level subcategories for each category branch a product belongs to, but from your post above it looks like your trying to build a breadcrumb list of one of the branches.

To directly answer your question at the end of the above post:
SELECT C.*, PC.*
FROM categories C INNER JOIN prodcategories PC ON C.categoryid = PC.categoryid
WHERE COALESCE(c.subcategories,'') = ''

Which makes me wonder, what DB are you using?

As I mentioned above, I don't think this is truly the answer your looking for. From your code it appears your trying to rebuild all the branches of categories for a specific product, not just find the last sub-categories for each branch.

Your category architecture doesn't seem to lend itself to easily being shown in a breadcrumb view. At some point a decision has to be made on which category trail to show and it doesn't seem the logic is there to display the one the user followed to get there, or a primary set of categories the product and like products belong to.
Category breadcrumbs really only exist for one reason, to help people find like products. I think in this case you need to restrict each product to only belonging in a single category/subcategory section to reduce the possible trails to the product to one. Any additional words you were using as categories or subcategories could be entered as keywords for the product, your breadcrumbs are easier to implement and more meaningful, and your structure (provided you pick good categories/subcategories) should be a lot more logical and easy to browse through.

-T

 

[JohnUS1]

Thanks for your input.

When visiting the subcategory, we only show the branch this category belongs too. But when running a product that can belong to multiple categories, we currently only show the first Last category available and all other are left out.

Breadcrumbs are useful either way - show one, or all available options.

A subcategory can be a (child and a) parent to another subcategory.
This is why "isParent" and "hasCategorys" are needed.
A Product would be only in a subcategory that has no child

INPUT: We have the productID to start with.
breadcrumb = productID[Name] (lookup in products)

Output:

[A.]
Get a subcategory (that has no child below it -lookup in categories) where productID belongs in. (lookup in prodcategories)

[B.]
Get this_subcategory [Name] and [Parent] (lookup in categories)
breadcrumb = subcategory[Name]URL & > breadcrumb

this_subcategory = [parent]
Loop [B.] until we're at top category

Loop [A.] (do next subcategory) until EOF

response.write breadcrumb
response.Thanks for your help

It's an access DB. Running on ASP windows based hosting.

 

[Tarwn]

Thanks for the additional informaiton, I think I understand better how your trying to build the breadcrumbs now. I think your last section in the above post is basically the answer. When you have this type of table structure for parent/child structures your going to end up doing recursion/looping somewhere along the way to traverse the tree.

Here is my restatement or take on what you stated above:

1) Define part number (or receive part number and place in variable)

2) Get the first subcategory, if one exists:
   Select TOP 1 C.CategoryName, C.CategoryID, C.HasParent, C.ParentID
   FROM ProdCategories PC Left Join Categories C
   WHERE PC.catalogId = [i]PartNumber[/i]

3) Get Parent Categories until there is no parent
   [u]Option A:[/u]
   Declare a boolean hasParent
   Declare a string BreadCrumbs
   Declare a number ParentID
   Assign the value from your first recordset HasParent to your boolean
   Assign the Parent ID Value from the first recordset
   Create your product link and first subcategory link from current recordset and place in Breadcrumbs string
   While HasParent = True
      GetParentCategory:
         SELECT TOP 1 C.CategoryName, C.CategoryID, C.HasParent, C.ParentID
         FROM Categories C
         WHERE CategoryID = [i]ParentID[/i]

      Build a link for this item, concatenate it to the front of your Breadcrumbs string
      Assign HasParent to local variable of same name
      Assign ParentID to local variable of same name

   After the loop output your newly built Breadcrumbs string
   
   [u]Option B: Pseudo-unrolling of the loop[/u]
   - not in the code: find the average number category depth for your products or the depth for 90% of your products

   - do everything like above but instead of querying for a single category at a time, we will be querying for the number found above minus one (we query for the first one outside the loop and Avg-1 inside):

   SELECT C1.CategoryName, C1.CategoryID, C1.ParentID, C1.HasParent, C2.CategoryName, C2.CategoryID, C3.CategoryName, C3.CategoryID, C4.CategoryName, C4.CategoryID
   FROM Categories C4 
      LEFT JOIN Categories C3 ON C4.ParentID = C3.CategoryID
      LEFT JOIN Categories C2 ON C3.ParentID = C2.CategoryID
      LEFT JOIN Categories C1 ON C2.ParentID = C1.CategoryID
   WHERE C4.CategoryID = [i]ParentID from first query[/i]

   If C4.CategoryName isn't null then concatenate a link for this category in breadcrumbs
   Repeat for C3
   Repeat for C2
   Repeat for C1
   Set the HasParent variable and ParentID variable from C1 so that if this one did have 4 + 1 categories and still has a parent it will loop to query again

I'm not sure which of these possible solutions will be more efficient, it comes down to how many records you have and the efficiency of pulling in X joined categories at a time vs creating X recordsets in the first option.


Another option that might increase performance and your ability to get the entire genealogy in one query would be something like the nested set model, but I think this would be harder and poorer performing to manage in an MS Access database compared to having compiled, stored procedures in SQL Server.

-T