SitePoint Sponsor

User Tag List

Results 1 to 10 of 10
  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)

    How to output this query?

    I'm referencing this thread:
    http://www.sitepoint.com/forums/showthread.php?t=164741

    I've finally got a query that appears to do what I need.

    PHP Code:
    SELECT cc.comp_cat_namecs.comp_specs_namesv.csv_value
    FROM tblcomp_categories cc
    INNER JOIN tblcomp_specs cs
        ON cc
    .comp_cat_id cs.fk_comp_cat_id
    INNER JOIN tblcomp_spec_values sv
        ON cs
    .comp_specs_id sv.fk_comp_specs_id
    WHERE sv
    .fk_prod_id 6
    ORDER BY cc
    .comp_cat_namecs.comp_specs_name 
    It returns this result set.
    comp_cat_name|comp_specs_name|csv_value
    Engine|Bore|110 mm / 4.33 in
    Engine|Displacement|7.2 L / 439 in3
    Engine|Engine Model|Cat 3126B
    Engine|Flywheel Power|108 kW / 145 hp
    Engine|Maximum Flywheel Power|127 kW / 170 hp
    Engine|Maximum Flywheel Power|9.7 kph / 6 mph
    Engine|Net Power - Caterpillar|108 kW / 145 hp
    Engine|Net Power - EU 80/1269|108 kW / 145 hp
    Engine|Net Power - ISO 9249|108 kW / 145 hp
    Engine|Net Power - SAE J1349|108 kW / 145 hp
    Engine|Stroke|127 mm / 5 in
    Transmission| 1 Forward|3.3 kph / 2 mph
    Transmission| 1 Reverse|4 kph / 2.5 mph
    Transmission| 2 Forward|5.7 kph / 3.5 mph
    Transmission| 2 Reverse|7 kph / 4.3 mph
    Transmission| 3 Reverse|12 kph / 7.4 mph


    Now the proble I have is spliiing out the information into a list that looks roughly like this:
    Engine
    Bore - 110 mm / 4.33 in
    Displacement - 7.2 L / 439 in3
    Engine Model - Cat 3126B
    Flywheel Power - 108 kW / 145 hp
    Maximum Flywheel Power - 127 kW / 170 hp
    Maximum Flywheel Power - 9.7 kph / 6 mph
    Net Power - Caterpillar - 108 kW / 145 hp
    Net Power - EU 80/1269 - 108 kW / 145 hp
    Net Power - ISO 9249 - 108 kW / 145 hp
    Net Power - SAE J1349 - 108 kW / 145 hp
    Stroke - 127 mm / 5 in

    Transmission
    1 Forward - 3.3 kph / 2 mph
    1 Reverse - 4 kph / 2.5 mph
    2 Forward - 5.7 kph / 3.5 mph
    2 Reverse - 7 kph / 4.3 mph
    3 Reverse - 12 kph / 7.4 mph

    I'm sure this is simple and I'm trying to figure it out on my end but a little help would also be aprpeciated. Anyone?

    and by the way Rudy...I wrote that query straight out, without even looking at a book. The feedback you've given me in the last few weeks has been unbelievably helpful.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  2. #2
    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)
    what you want to do is output the query as a group, then loop over the results in each group, putting the name of the group at the top before the loop.

    dont have the time to write the code, but i think you can get it
    rynoguill
    Ryan Guill, AKA Mark Roman

  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)
    So add a GROUP BY comp_cat_name (Engine,Transmission, etc) in the initial query, then proceed from there. I'll give that shot.
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  4. #4
    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)
    Quote Originally Posted by creole
    So add a GROUP BY comp_cat_name (Engine,Transmission, etc) in the initial query, then proceed from there. I'll give that shot.
    no no no, youre query is fine, see how its already grouping the items?

    you need to add group by to your cfoutput tag

    (i dont think group by is an sql operator anyway, so im sure youve probably already noticed this)
    rynoguill
    Ryan Guill, AKA Mark Roman

  5. #5
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by Rynoguill
    no no no, youre query is fine, see how its already grouping the items?

    you need to add group by to your cfoutput tag

    (i dont think group by is an sql operator anyway, so im sure youve probably already noticed this)
    Actually, GROUP BY is a SQL operator...

    But, Andy, you should use <cfoutput group="comp_cat_name"> then inside it, <cfoutput group="comp_specs_name">... (I think). You may have to add the fields into a GROUP BY statement in your query. I don't remember if CF requires the for using <cfoutput group=""> or not...

  6. #6
    SitePoint Wizard creole's Avatar
    Join Date
    Oct 2000
    Location
    Nashvegas Baby!
    Posts
    7,845
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks guys. This code did the trick. I knew I could figure it out with a little prodding:

    PHP Code:
    <cfoutput query="getCompCats" group="comp_cat_name">
        
    #comp_cat_name#
        
    <cfoutput group="comp_specs_name">
            
    #comp_specs_name# - #csv_value#
        
    </cfoutput>
    </
    cfoutput
    by the way, why won't Loop work in the inner position?
    Adobe Certified Coldfusion MX 7 Developer
    Adobe Certified Advanced Coldfusion MX Developer
    My Blog (new) | My Family | My Freelance | My Recipes

  7. #7
    SitePoint Wizard davidjmedlock's Avatar
    Join Date
    Dec 2002
    Location
    Nashville, TN USA
    Posts
    1,688
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by creole
    Thanks guys. This code did the trick. I knew I could figure it out with a little prodding:

    PHP Code:
     <cfoutput query="getCompCats" group="comp_cat_name">
         
    #comp_cat_name#
         
    <cfoutput group="comp_specs_name">
             
    #comp_specs_name# - #csv_value#
         
    </cfoutput>
     </
    cfoutput
    by the way, why won't Loop work in the inner position?
    Loop doesn't have a group attribute. It's only available with output... Personally, I think it's a flaw in the design of CFML. CFLOOP tends to be more efficient than CFOUTPUT, so they should have given it the functionality of looping by group...

  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)
    somethings not right about that code.... im having trouble putting my finger on it though. i think its a concept ive got messed up...

    youre not going to have multiple items with the same spec name are you? cause if not then you dont need group in the inner statment, you should be able to do cfloop on the query... not enough sleep... brain isnt working.... let me think about this.
    rynoguill
    Ryan Guill, AKA Mark Roman

  9. #9
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    aw, crap (to quote a popular phrase on "everybody loves raymond")

    i step out for a few hours and look at the fun you guys have had without me

    thanks for the kind words, andy, and way to go on developing your query

    i have added your remarks to my special "kudos" file, which i pull out once in a while to remind me why i answer questions on database forums

    i agree, something is not quite right with that last code

    you are allowed to nest multiple CFOUTPUT tags with the GROUP= parameter, for multi-level control breaks, but i believe the innermost CFOUTPUT usually does not have the GROUP= parameter (otherwise, you have returned a level of detail that you did not need, at least not in this set of nested CFOUTPUTs)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  10. #10
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    p.s. GROUP BY in the sql and GROUP= in the CFOUTPUT are two completely different concepts

    what comes out of the SELECT is a result set, and what matters to the correct functioning of the GROUP= parameters is that the results be in a specific sequence -- namely, the sequence of the columns involved in the GROUP= nesting

    does that make sense?

    the GROUP BY might be involved in the sql, but that is incidental

    it is the ORDER BY that matters
    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
  •