9 Replies Latest reply on Jun 1, 2006 6:25 AM by gendrall

    cfoutput group 2 column split help

    gendrall
      I have an output that I'd like to split up into 2 columns. I've done it before using a recordcount, divide by 2, one column has a maxrows in the output, the second has startrow in the output. This way is no longer working for me as my cfoutput, with a group, is not spliting things up in an even manner like I need it to.

      Here's my code, without a split:

      <cfquery name="getInfo" datasource="#ds#">
      Select hotel.active, hotel.hotelName, hotel.HotelID, region.Region, city.city, country.country, State.State
      From region, HotelLocation, hotel, city, country, State
      Where region.RegionID = HotelLocation.RegionID
      and Hotel.HotelID = HotelLocation.HotelID
      and city.CityID = HotelLocation.CityID
      and Country.CountryID = HotelLocation.CountryID
      and State.StateID = HotelLocation.StateID
      and HotelLocation.RegionID = #url.RegionID#
      and HotelLocation.active = 1
      order by Country.country, state.state, hotel.hotelName, city.city
      </cfquery>

      <cfoutput query="getInfo" group="Country" maxrows="#startCol#">
      <b>::: #country# :::</b> #startCol#<br />
      <cfoutput group="State">
      <cfif #State# is NOT 'NULL'> <b>- #State# -</b><br /></cfif>
      <cfoutput>
        <a href="browse_hotel.cfm?hotelID=#hotelID#">#hotelName#</a><!---<cfif #city# is NOT 'NULL'> - #city#</cfif>---><b /r>
      </cfoutput><p></p>
      </cfoutput>
      </cfoutput>
      ----------
      I want to have an even number split, not just of countries, but of the results. So if the country needs to be split in 2 colums that is fine. Like if the region is north america, the countries that are there displayed are Bermuda, Canada and the US. Bermuda has one hotel, Canada has several (but for some reason doesn't show), but the US has all 50 states in one column. And Bermuda is all by itself...a bit off balanced since in the other column that is displayed has all 50 states.

      Any one have a solution? Confused as to what I'm trying to say?
        • 1. Re: cfoutput group 2 column split help
          Dan Bracuk Level 5
          quote:

          Originally posted by: gendrall
          I have an output that I'd like to split up into 2 columns. I've done it before using a recordcount, divide by 2, one column has a maxrows in the output, the second has startrow in the output. This way is no longer working for me as my cfoutput, with a group, is not spliting things up in an even manner like I need it to.

          Here's my code, without a split:
          I want to have an even number split, not just of countries, but of the results. So if the country needs to be split in 2 colums that is fine. Like if the region is north america, the countries that are there displayed are Bermuda, Canada and the US. Bermuda has one hotel, Canada has several (but for some reason doesn't show), but the US has all 50 states in one column. And Bermuda is all by itself...a bit off balanced since in the other column that is displayed has all 50 states.

          Any one have a solution? Confused as to what I'm trying to say?


          You can use query of queries to get the number of countries and the number of states. If you add the query record count to triple the number of countries and triple the number of states, you should get the number of lines you want to display. Divide that by two and you're in business.

          By the way, the nested grouping my be why you are seeing unexpected results. Try grouping by country only and use if/else logic to determine when the name of the state changes.
          • 2. Re: cfoutput group 2 column split help
            gendrall Level 1
            You mean a query in the outputs or sub queries in the initial query?

            What about looping over a ValueList?

            I don't want to have too many queries on the page since there will be a lot of results and I don't want to hammer the server too much :-]


            • 3. Re: cfoutput group 2 column split help
              Dan Bracuk Level 5
              quote:

              Originally posted by: gendrall
              You mean a query in the outputs or sub queries in the initial query?

              What about looping over a ValueList?

              I don't want to have too many queries on the page since there will be a lot of results and I don't want to hammer the server too much :-]


              Query of queries does not touch the db.
              • 4. Re: cfoutput group 2 column split help
                gendrall Level 1
                Query of queries?
                Well, you've got me stumped. I've never actually worked with a query of queries and ashamed to say I've never heard of such a thing. How does one go about using them?
                • 5. Re: cfoutput group 2 column split help
                  paross1 Level 2
                  As long as you aren't using ColdFusion 4.5 you should be able to use Q-of-Q.
                  • 6. Re: cfoutput group 2 column split help
                    Dan Bracuk Level 5
                    quote:

                    Originally posted by: gendrall
                    Query of queries?
                    Well, you've got me stumped. I've never actually worked with a query of queries and ashamed to say I've never heard of such a thing. How does one go about using them?


                    Assuming you are using CF Version 5 or higher, google "coldfusion query of queries" The first offering is a tutorial.
                    • 7. Re: cfoutput group 2 column split help
                      gendrall Level 1
                      I'm using CF7 :-]
                      So we are good with the query of queries. Very nice.

                      My main problem now is the I've never done a grouping with if/else statements, I've only used them in cfouputs.
                      • 8. Re: cfoutput group 2 column split help
                        gendrall Level 1
                        I've been using the query of queries and it's not producing the results that I need. The groupings are off and the column split that I need is either off or completely ignored.
                        • 9. Re: cfoutput group 2 column split help
                          gendrall Level 1
                          Ok, I've figured out a work around.

                          I basically use the query of queries, got the country count, split the count in 2 and used maxrows for the first column. Then I did a row count of the first column, added a 1 to that number to get my startrow position for the 2nd column.

                          :-]