Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 9 of 9
  1. #1
    New to the CF scene
    Join Date
    Sep 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts

    multiple catgories recordset loop for breadcrumb display

    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.

    Code:
    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 [red]categories[/red] "
    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

  • #2
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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?

  • #3
    New to the CF scene
    Join Date
    Sep 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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
    Last edited by JohnUS2; 09-04-2007 at 08:45 PM.

  • #4
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    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.
    Code:
    "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:

    Code:
    "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?

  • #5
    Senior Coder
    Join Date
    Dec 2002
    Location
    Arlington, Texas USA
    Posts
    1,062
    Thanks
    4
    Thanked 8 Times in 8 Posts
    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

  • #6
    New to the CF scene
    Join Date
    Sep 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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 [B.] until we're at top category

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

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

  • #7
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    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-...archies-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

  • #8
    New to the CF scene
    Join Date
    Sep 2007
    Posts
    4
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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?

  • #9
    Senior Coder Spudhead's Avatar
    Join Date
    Jun 2002
    Location
    London, UK
    Posts
    1,856
    Thanks
    8
    Thanked 110 Times in 109 Posts
    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


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •