3 Replies Latest reply on Oct 8, 2016 3:29 AM by BKBK

    Weird subquery result in output, but not in CFDUMP or query run in MySQL Workbench

    WolfShade Level 4

      Hello, all,

       

      I've got a query that is grabbing picture album names, the associated "cover" image for each album, other bits of information for each picture album, and a subquery to grab the total number of images per album.

       

      If I run the query in MySQL Workbench, everything is correct.  If I do a CFDUMP of the query, everything is correct.

       

      When I output the results to the page, the values for number of images per album is concatenating all of the values, and decrementing the number of (number of images) per iteration of the output.

       

      For example, let's pretend that my query as run in MySQL Workbench looks like:

      albumID          name          caption          active          imageID          imageWidth          imageHeight          totImgs
      ==================================================================================================================================
      {uuid}           July 4, 2003                      1               {uuid}        896                 600                      50
      {uuid}           Benefit                           1               {uuid}        1000                750                      47
      {uuid}           Hooters 2006                      1               {uuid}        1000                750                      72
      

       

      First album has 50 images, second album has 47 images, third album has 72 images.

       

      In CFDUMP, everything is as it is for MySQL Workbench (above).  But for CFOUPUT, when trying to output the number of images per album, I get:

       

      504772

      4772

      72

       

      It's concatenating all three values for the first output, the second and third values for the second output, and just the third value for the third output.

       

      What could be causing this?

       

      V/r,

       

      ^_^

        • 1. Re: Weird subquery result in output, but not in CFDUMP or query run in MySQL Workbench
          BKBK Adobe Community Professional & MVP

          WolfShade wrote:

           

          504772

          4772

          72

          The structure suggests you might be outputting within 2 loops. Something like

           

          <loop1 index="idx1"><!--- index idx1 ranges from 1 to 3--->

          <loop2 index="idx2"><!--- index idx2 ranges from idx1 to 3--->

          output nr

          </loop2>

          </loop1>

          • 2. Re: Weird subquery result in output, but not in CFDUMP or query run in MySQL Workbench
            WolfShade Level 4

            Hi, BKBK,

             

            Negative.. no inner loops (I avoid those, whenever possible.)

             

            Although.. now that you mention it.. without realizing it, there's a chance that I may have inadvertently done something like:

            <cfoutput query="myQry">
                 Blah blah blah
                 Foo Bar Foo Bar  Fubar
                 <cfoutput>#totImgs#</cfoutput>
                 Snafu Snafoo
            </cfoutput>
            

             

            Some people put CFOUTPUT tags around the entire document and use CFLOOP for query output.  I'm the kind who uses CFOUTPUT only around variables and whatnot; so it's entirely possible (likely, even) that I added CFOUTPUT around a variable that didn't need it because it's already inside a query CFOUTPUT.  But I thought that this could happen only if the outer CFOUTPUT used the group attribute???

             

            IDK.. I'll check my code when I get home, and report back.

             

            Thanks, and V/r,

             

            ^_^

            • 3. Re: Weird subquery result in output, but not in CFDUMP or query run in MySQL Workbench
              BKBK Adobe Community Professional & MVP

              WolfShade wrote:

               

              Although.. now that you mention it.. without realizing it, there's a chance that I may have inadvertently done something like:

              1. <cfoutputquery="myQry">
              2. Blahblahblah
              3. FooBarFooBarFubar
              4. <cfoutput>#totImgs#</cfoutput>
              5. SnafuSnafoo
              6. </cfoutput>

               

              Coldfusion is known to produce unexpected results within nested cfoutput tags. So I would play it like this

               

              <cfoutput query="myQry">

              Blahblahblah

              FooBarFooBarFubar

              #totImgs#

              SnafuSnafoo

              </cfoutput>