5 Replies Latest reply on Jun 3, 2006 10:47 PM by Sleinaddet

    cfloop query not working as expected.

    murpg Level 1
      Hi I am trying to create a sitemap from a dynamic web site. I am using 2 cfloop queries to do this. My problem is that on all of the second level items I have the same header ID. How can I fix this? Here is my code. The #t2name# link ID=#qGetSite.T1ID# keeps coming up with 10 it is not looping properly. Any help would be most appreciated.



        • 1. cfloop query not working as expected.
          Dan Bracuk Level 5
          Why don't you just use one query and join on t1idfk = t1id? Also, in your loop, you have to specify the row number.
          • 2. cfloop query not working as expected.
            murpg Level 1
            Dan, thanks for your suggestions. After beating on it a while I was able to get it to work properly. I would like to add another element to it. Any suggestions on that would help also. I want to add a third table called content whose foreign key is location. This would be part of the ON statement in SQL. ON Tier1ID = Location and ON Tier2ID = Location, so my question is how do I integrate that into my SQL statement for Access and how do I display it? I would want something like this.

            Tier1.T1Name
            Tier2.T2Name
            Mistakenly posted see below.

            • 3. Re: cfloop query not working as expected.
              murpg Level 1
              Dan, thanks for your suggestions. After beating on it a while I was able to get it to work properly. I would like to add another element to it. Any suggestions on that would help also. I want to add a third table called content whose foreign key is location. This would be part of the ON statement in SQL. ON Tier1ID = Location and ON Tier2ID = Location, so my question is how do I integrate that into my SQL statement for Access and how do I display it? I would want something like this.

              Tier1.T1Name
              Tier2.T2Name
              Location
              Tier1.T1Name
              Location
              Tier1.T1Name
              Tier1.T1Name
              Tier2.T2Name
              Tier2.T2Name
              Tier2.T2Name

              Here is my code.
              <CFQUERY NAME="qGetSite" DATASOURCE="#application.ds#">
              SELECT * From Tier1 LEFT OUTER JOIN Tier2
              ON Tier1.t1id = CInt(Tier2.t1idfk)
              UNION
              SELECT * From Tier1 RIGHT OUTER JOIN Tier2
              ON Tier1.t1id = CInt(Tier2.t1idfk)
              ORDER BY Tier1.ButtonLoc, Tier2.ButtonLoc;
              </CFQUERY>
              <CFOUTPUT QUERY="qGetSite" group="T1Name">
              <A HREF="level1.cfm?ID=#qGetSite.T1ID#" CLASS="button" style="width:100%; text-align:left;">#qGetSite.T1Name#</A><br />
              <cfoutput>
              <cfif qGetSite.T2ID IS NOT ""><A HREF="level2.cfm?ID=#qGetSite.T1ID#&T2ID=#qGetSite.T2ID#" CLASS="buttonlevelsitemap">> #qGetSite.t2name#</a><br /><cfelse></cfif>
              </cfoutput>
              </CFOUTPUT>

              • 4. Re: cfloop query not working as expected.
                Dan Bracuk Level 5
                Your sql is much too complicated and you didn't say what content.location is related to. I'll assume you meat that a field in content joins to tier1.t1id. Your sql would simply be:

                select t1name, t2name, location
                from tier1, tier2, content
                where t1id = t1idfk
                and t1id = some_field_in_content

                to display it do this

                <cfoutput query = "theOneAbove">
                #t1name#<br />#t2name#<br />#location#<br />
                • 5. Re: cfloop query not working as expected.
                  Sleinaddet
                  Seems to me you'd be better combining the queries, sort by Tier1 then by Tier2 and then output using
                  <CFOUTPUT Query="" Group="Tier1">
                  #Tier 1#
                  <CFOUTPUT>
                  #Tier 2#
                  </CFOUTPUT>
                  </CFOUTPUT>
                  and let CF do the grouping and subheadings