...

View Full Version : multiple catgories recordset loop for breadcrumb display



JohnUS2
09-03-2007, 05:56 AM
Hi all,

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 category the product is from
sql="SELECT products.cname, categories.categoryid FROM categories "
sql=sql & "INNER JOIN (products "
sql=sql & "INNER JOIN prodcategories 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 & " > " & cathead 'separator
End If
catid = rs("highercategoryid")
Else
Closerecordset rs
Exit Do
End If
Closerecordset rs
Loop

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



3 tables are used for this function

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 hassubcategories<>"yes") that are included in prodcategories and output their structure via a loop.

Any help would be greatly appreciated

miranda
09-04-2007, 08:20 PM
Is prodcategories the table that you are holding all of the categories in? Is the field categoryid a list of some sort perhaps a comma delimited list?

JohnUS2
09-04-2007, 09:43 PM
Is prodcategories the table that you are holding all of the categories in? yes and no - see below
Is the field categoryid a list of some sort perhaps a comma delimited list?

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

Spudhead
09-05-2007, 02:41 PM
I'm still not sure what your goal is here.

Let me get the basic rules straight:
- You've got products, and categories.
- Categories can either be parent categories, or subcategories of parent categories.
- Products can be linked only to subcategories, not to parent categories.
- A subcategory can be linked to only one parent category.
- There is only one level of subcategoryness: a subcategory cannot be a parent to any other subcategories.
- You're linking products to subcategories via a "prodcategories" table that simply tracks a product id and a subcat id.

That about right? If so, I'd drop all this "hasCategorys" and "isParent" stuff.

You need three tables:
tblProducts: id, productname
tblCategories: id, categoryname, parentid
tblProdCats: id, productid, categoryid

If parentid=0 then it's a category. Otherwise, it's a subcategory.

Right:

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


"SELECT c.id, c.categoryname, sc.id, sc.categoryname FROM tblCategories c INNER JOIN tblCategories sc ON sc.parentid = c.id ORDER BY c.categoryname"

Assuming that you've not got any categories in there with ID=0, that will get you a list of categories with all their subcategories. And this will get you the full breadcrumb trail for any product:


"SELECT c.id, c.categoryname, sc.id, sc.categoryname, p.id, p.productname FROM tblCategories c INNER JOIN tblCategories sc ON sc.parentid = c.id INNER JOIN tblProducts p ON p.categoryid = sc.id WHERE p.id = " & myProduct ID


Is that any use at all? What input are you expecting, and what do you actually want to output?

miranda
09-06-2007, 12:22 AM
To carry Spudheads work one step further you don't need the id field in the table prodcategories since each field is a foreign key to the other two tables, so that sets your index.

tblProducts: ProductID, productname
tblCategories: CategoryID, categoryname, parentid
tblProdCats: productid, categoryid

JohnUS2
09-06-2007, 05:26 AM
Spudhead, Your description of project is correct except the 5th line (one before last)
There is only one level of subcategoryness: a subcategory cannot be a parent to any other subcategories.

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


What input are you expecting, and what do you actually want to output?

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 until we're at top category

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

response.write breadcrumb
response.Thanks for your help
response.:)

Spudhead
09-06-2007, 04:10 PM
Aha. Well, the good news is that there's a thing you can do in SQL that, IIRC, does exactly this, and it's called a recursive join. The bad news is I've been staring at examples for the last ten minutes and I still can't figure it out. Mind, my SQL is patchy at best. Have a look at these: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=88675
http://www.sqlteam.com/article/more-trees-hierarchies-in-sql
or just Google "SQL recursive join", there's loads of discussions and examples out there. But be prepared for some very confusing talk about nested sets and the adjacency model.

ps Miranda - yeah I know. But old habits die hard :)

JohnUS2
09-06-2007, 08:27 PM
Spudhead,
Thanks for your input.

I can have this setup via other methods, the old step by step way, but it would take up time for each load, which is NOT good for any webpage.

The SQL recursive join you mentioned would save the time?

Spudhead
09-10-2007, 03:24 PM
Yes, exactly - you'd only be running one SQL statement, no matter how deep your breadcrumbs got. It might be a bigger and more complex statement but I'd put money on it running a lot faster than lots of little ones. And you get the added bonus of sticking the solution up here and looking good :thumbsup:



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum