SitePoint Sponsor

User Tag List

Page 1 of 2 12 LastLast
Results 1 to 25 of 27
  1. #1
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    breadcrumb navigation with recursively nested table?

    So I was reading up on a thread which took place a year or two back. It talked about using recursive tables for a dmoz type category listing. Iv'e got it set up and it works great. I'd like input on the best way to create a breadcrumb navigation trail though. The categories will never go more than 3 levels deep (parent > firstchild > secondchild)

    Let's go over some of my data (zero indicates the category has no parent, thus top level):
    PHP Code:
    cat_id|cat_name|cat_childof
    1
    |Abrasives|0
    2
    |Air Compressors|0
    3
    |Air Tools|0
    skip ahead
    40
    |Cut-Off Wheels-Chop Saws|1
    45
    |Flooring-Sanding Rolls|1
    46
    |Sandpaper-Aluminum Oxide|
    Right now I have no products in my db, I'm simply at a testing phase with this category structure. I've used it before and it works but I'm not 100% that it's the best way.

    What I want to do is to display navigation like so

    top level > first child > current category

    This is what I've got right now:
    PHP Code:
    <html>
    <
    head>
    <
    title> new document </title>
    </
    head>
    <
    body>

    <
    cfquery name="getCategories"
    dataSource="#Application.DSN#"
    dbType="#Application.DBType#"
    username="#Application.username#"
    password="#Application.password#">
        
    SELECT cat_idcat_name
        FROM tblprodcategories
        
    <cfif parameterexists(URL.catid) AND URL.catid IS NOT "">
            
    WHERE cat_childof #URL.catid#
        
    <cfelse>
            
    WHERE cat_childof 0
        
    </cfif>
    </
    cfquery>

    <
    cfif parameterexists(URL.catid)>
        <
    cfquery name="getCategoryName"
        
    dataSource="#Application.DSN#"
        
    dbType="#Application.DBType#"
        
    username="#Application.username#"
        
    password="#Application.password#">
            
    SELECT cat_name
            FROM tblprodcategories
            WHERE cat_id 
    #URL.catid#
        
    </cfquery>
        <
    cfoutput><h3>#getCategoryName.cat_name#</h3></cfoutput>
    <cfelse>
        <
    cfoutput><h3>Product Categories</h3></cfoutput>
    </
    cfif>


    <
    cfoutput query="getCategories">
        <
    a href="categories.cfm?catid=#cat_id#">#cat_name#</a><br>
    </cfoutput>

    </
    body>
    </
    html
    If URL.catid does not exist then it selects everything with no parent (WHERE cat_childof = 0). If it does exist, it selects everything that is a child of the current URL.catid. Then in another query, it selects the categoryname for the current URL.catid.

    Any thoughts?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i don't see why you have two separate queries

    in order to do the breadcrumb, you need a three-table self-join

    typically, you go from a subsubcat to a subcat to a cat in order to find the breadcrumb trail, and then print it in the other order

    if you know what i mean

    bring some products into the mix and i'll show you the query

    by the way, whose posts from a year ago were you reading?


    p.s. what does "State of Bliss (see sig)" mean?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    The sig is from when I got married. It's probably okay to take it down now since we've been married for almost a year now.

    I'll get some products in there today and show you later. Thanks for your help Rudy. I greatly appreciate it.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    you got married to michael jackson?

    or one of his boyfriends?

    what's an outwar?

    when i go to that site, there's a dark, brown picture of a monster

    is that your spouse?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Guru nagrom's Avatar
    Join Date
    Jul 2001
    Location
    Western CT, USA
    Posts
    803
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    did you know mork and mindy was really about michael jackson? how did he go from an 8-year-old James Brown to a 40 year-old Carol Chaning? I think we need some answers before colonization of the moon begins.

  6. #6
    minister of propaganda silver trophy Rynoguill's Avatar
    Join Date
    Feb 2004
    Location
    Midsouth
    Posts
    1,373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    you guys are cracking me up, and that outwar site ticked me off with the javascript popup box
    rynoguill
    Ryan Guill, AKA Mark Roman

  7. #7
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    mark, hie thee hence quickly to the google site and install the google toolbar

    no more popups

    not using internet exploder? then there are bookmarklets that will do the same thing

    in fact, in firebirdfox and mozilla, turning javascript off is dead simple...

    holler if thee needest instructions for same
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  8. #8
    minister of propaganda silver trophy Rynoguill's Avatar
    Join Date
    Feb 2004
    Location
    Midsouth
    Posts
    1,373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thee is much admired for thee adivice, hark, verily i am using firefox, the much lauded browser, verily i say it is the best, alas, i cannot bring myself to turn off thee javascript, tis too much of a burden, alas, more than i can bear!
    rynoguill
    Ryan Guill, AKA Mark Roman

  9. #9
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Alrighty...

    Here's the brief table structure for the site thus far.
    PHP Code:
    # Table structure for table `tblmanufacturers`
    CREATE TABLE tblmanufacturers (
      
    mfr_id smallint(5unsigned NOT NULL auto_increment,
      
    mfr_name varchar(60NOT NULL default '',
      
    PRIMARY KEY  (mfr_id)
    TYPE=MyISAM;


    # Table structure for table `tblprodcategories`
    CREATE TABLE tblprodcategories (
      
    cat_id smallint(4unsigned NOT NULL auto_increment,
      
    cat_name varchar(75NOT NULL default '',
      
    cat_childof tinyint(4unsigned NOT NULL default '0',
      
    PRIMARY KEY  (cat_id)
    TYPE=MyISAM;


    # Table structure for table `tblproducts`
    CREATE TABLE tblproducts (
      
    prod_id mediumint(8unsigned NOT NULL auto_increment,
      
    fk_mfr_id smallint(5unsigned NOT NULL default '0',
      
    prod_name varchar(50NOT NULL default '',
      
    fk_cat_id smallint(5unsigned NOT NULL default '0',
      
    PRIMARY KEY  (prod_id),
      
    KEY fk_mfr_id (fk_mfr_id,fk_cat_id)
    TYPE=MyISAM
    Now, here's a data sample from the two new tables, tblproducts and tblmanufacturers.

    tblmanufacturers
    mfr_id|mfr_name
    1|Bobcat Co.
    2|Case Corp.
    3|CATERPILLAR

    tblproducts
    prod_id|fk_mfr_id|prod_name|fk_cat_id
    1|5|XP185WIR|2
    2|2|580M 4
    3|3|416D|4
    4|4|310G|4
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Code:
    insert into tblmanufacturers values
     (1, 'Bobcat Co.')
    ,(2, 'Case Corp.')
    ,(3, 'CATERPILLAR')
    ;
    
    insert into tblprodcategories values
     (1, 'Abrasives', 0 )
    ,(2, 'Air Compressors', 0 )
    ,(3, 'Air Tools', 0 )
    ,(40, 'Cut-Off Wheels-Chop Saws', 1 )
    ,(45, 'Flooring-Sanding Rolls', 1 )
    ,(46, 'Sandpaper-Aluminum Oxide', 1 ) 
    ;
    
    insert into tblproducts values
     (1, 5, 'XP185WIR', 2)
    ,(2, 2, '580M', 4)
    ,(3, 3, '416D', 4)
    ,(4, 4, '310G', 45) -- to make the results more interesting
    ;
    
    --------------------------------------------------
    -- first, the CSI "so, what do we got here?" query
    --------------------------------------------------
    
    select p.prod_id
         , p.prod_name
         , coalesce(mfr_name,'manufacturer missing!') as manufacturer
         , coalesce(cat_name,'(sub)category missing!') as category
      from tblproducts p
    left outer
      join tblmanufacturers m
        on p.fk_mfr_id = m.mfr_id
    left outer
      join tblprodcategories c
        on p.fk_cat_id = c.cat_id
    order 
        by p.prod_id
        ;    
        
    prod_id,prod_name,manufacturer,category
    1,XP185WIR,manufacturer missing!,Air Compressors
    2,580M,Case Corp.,(sub)category missing!
    3,416D,CATERPILLAR,(sub)category missing!
    4,310G,manufacturer missing!,Flooring-Sanding Rolls
    
    ---------------------------------------------
    -- now, the precursor-to-the-breadcrumb query
    ---------------------------------------------
    
    select p.prod_name
         , coalesce(level0.cat_name,'') as level0cat
         , coalesce(level1.cat_name,'') as level1cat
         , coalesce(level2.cat_name,'') as level2cat
      from tblproducts p
    left outer
      join tblprodcategories level0
        on p.fk_cat_id = level0.cat_id
    left outer
      join tblprodcategories level1
        on level0.cat_childof = level1.cat_id
    left outer
      join tblprodcategories level2
        on level1.cat_childof = level2.cat_id
    order 
        by p.prod_name
         , level0cat
         , level1cat
         , level2cat
         
    prod_name,level0cat,level1cat,level2cat
    310G,Flooring-Sanding Rolls,Abrasives,
    416D,,,
    580M,,,
    XP185WIR,Air Compressors,,
    
    --------------------------------
    -- finally, the breadcrumb query
    --------------------------------
    
    -- NOTE THAT IT IS DRIVEN BY A SINGLE PRODUCT
    
    select concat(
           coalesce(concat(level2.cat_name,' > '),'') 
         , coalesce(concat(level1.cat_name,' > '),'') 
         , coalesce(concat(level0.cat_name,' > '),'') 
         , p.prod_name
                 ) as breadcrumb
      from tblproducts p
    left outer
      join tblprodcategories level0
        on p.fk_cat_id = level0.cat_id
    left outer
      join tblprodcategories level1
        on level0.cat_childof = level1.cat_id
    left outer
      join tblprodcategories level2
        on level1.cat_childof = level2.cat_id
     where p.prod_id = 4
    
    breadcrumb
    Abrasives > Flooring-Sanding Rolls > 310G
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  11. #11
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Are you speaking English Rudy? Does mySQL offer the Coalesce function? I've seen it but never used it. If I understand my reference and your example, it lets you conglomerate several pieces of returned data into one "variable" (if you will)?

    Besides...you said earlier that it could be done in one query.
    Last edited by creole; Apr 13, 2004 at 09:48.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    actually, i was speaking sql

    i do believe you have successfully grokked the coalesce function

    i said the breadcrumb query is one query, and lo, it is
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  13. #13
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Google gives me results for COALESCE on the mySQL website when even their OWN search doesn't.

    interestingly enough, searching for "COALESCE, mysql" returns a resultset with YOUR NAME as the 4th record.

    I read that thread from devshed and you say "the coalesce function returns the first non-null value in its list". That seems different from my guess. Is it really the same in effect?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  14. #14
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By the way...thanks for your answer...I'm working through your code right now.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  15. #15
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    search on anything related to sql, and you will find me



    try your search mysql.com again:
    http://www.mysql.com/search/?q=coalesce

    it gives this as the first result: Comparison Operators

    admittedly, their examples are often (as in this case) lame


    coalesce(a,b,c,d,937) will return the first non-null value going from left to right

    most of the times you see coalesce, it has a hard-coded literal as the last item in the list, so that its final value cannot be null

    occasionally you want a null as the result, but those occasions are rare
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  16. #16
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    search on anything related to sql, and you will find me
    Even if you were just joking around, it's true. I find it pretty stunning that someone as learned as you would be hanging out on a message board like this one. Sure it's cool but you've got loads of people who don't know mySQL from theirELBOW.

    Regardless, I'd like to reiterate that I appreciate your help.

    So another question...what's the benefit in using COALESCE combine the result as opposed to doing the same thing in CF?

    Also, I've implemented your code into a sample page which merely dumps the query.
    http://www.pdqequipment.com/query.cfm

    Do I need ALL of these queries one page or where you just giving me examples?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  17. #17
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    By the way...I will never have a category or manufacturer that is missing so I removed those coalesce functions from the first query.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  18. #18
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "what's the benefit in using COALESCE combine the result as opposed to doing the same thing in CF?"

    i'd've thought that would be obvious -- less CF code

    re: http://www.pdqequipment.com/query.cfm

    that's very nice

    now, let's see you expand the categories so that there actually is more than one level

    breadcrumb trail looks pretty meagre with just one level

    "Do I need ALL of these queries one page or where you just giving me examples?"

    wanted to let you understand your own data structure

    the only query you need is the breadcrumb one

    thanks for the kind words, and the reason i hang out here on these forums is because i don't have a job, and i need to stay sharp, stay in shape, handle as many sql problems as i possibly can (over 10,000, when you count all the different forums i'm on)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  19. #19
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by r937
    "what's the benefit in using COALESCE combine the result as opposed to doing the same thing in CF?"

    i'd've thought that would be obvious -- less CF code
    But more SQL code. I guess my thought would be just to pull back the data and then be able to manipulate it anyway that I need to.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  20. #20
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    more sql code? in general, no

    oh, i'm sure you can construct an example where the code needed for some effect is smaller in cf than in sql

    i can too

    but in general, doing something with sql is better than doing it with code

    way, way better

    for lots of different reasons

    feel the force, luke
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  21. #21
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Cool...

    Thanks Rudy. You rock, as usual.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  22. #22
    minister of propaganda silver trophy Rynoguill's Avatar
    Join Date
    Feb 2004
    Location
    Midsouth
    Posts
    1,373
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    this is a great thread, ive learned a lot just reading through it. shouldnt it also be posted in the db/sql forum too to help everyone? just a thought. its not ever day people discuss COALESCE...
    rynoguill
    Ryan Guill, AKA Mark Roman

  23. #23
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Well I originally thought it would be more of a CF question but Rudy showed me that it could be otherwise. I still don't really understand how I would use that code.

    For example, how would my breadcrumb change based on the current category? How would my nav know when I have products in the category? How do you get your dishes so spot-free?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  24. #24
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    "how would my breadcrumb change based on the current category?"

    the query that i wrote gives you the breadcrumb to a specific product

    obviously, if you have a "category list" page, you simply (heh) remove the product table from the query and write the WHERE clause for the lowest (3rd) level category

    note that you could also feed the WHERE clause a cat_id value that was actually at the 2nd level, and the query would still work

    amazing!
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  25. #25
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    p.s. this will be covered in the course i am currently writing

    more details soon

    remind me if i forget
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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