6 Replies Latest reply on Apr 7, 2011 3:25 PM by ilssac

    Referring to Previous Entry In Query Loop

    wcx08 Level 1

      I'm currently working with a large amount of data in a query, where I am looping through the query and working with each row of the query individually.  I'm using:

       

      <cfloop query="qryData">

           #data1#

           #data2#

           etc...

      </cfloop>

       

      However, sometimes I will need to be able to refer to data in the previous row of the query, and compare it to data in the current row of the loop.  Is this possible to do?  Something like:

       

      <cfloop query="qryData">

           #data1#

           #qryData[i-1].data2#

           etc....

      </cfloop>

       

      Clearly, that's not the correct syntax, but I'm unsure of what is.

       

      Any information would be greatly appreciated.

       

      Thanks,

        • 1. Re: Referring to Previous Entry In Query Loop
          Owain North Level 4

          CFLOOP QUERY is one of the handy little CF shortcuts, looks like you'll have to go manual

           

          <cfloop from="1" to="#myquery.recordcount#" index="i">

            <cfoutput>#myquery.mycolumn[i]# #myquery.mycolumn[i-1]#</cfoutput>

          </cfloop>

           

          Obviously you'll need to put in some logic for the first row to stop it referencing row 0, which won't exist.

           

          Hth.

          • 2. Re: Referring to Previous Entry In Query Loop
            ilssac Level 5

            wcx08 wrote:


            <cfloop query="qryData">

                 #data1#

                 #qryData[i-1].data2#

                 etc....

            </cfloop>

             

            Clearly, that's not the correct syntax, but I'm unsure of what is.

             

            But it was pretty close:

             

            <cfloop query="qryData">
                 #data1#
                 #qryData.data2[qryData.currentRow - 1]#
                 etc....
            </cfloop>
            
            • 3. Re: Referring to Previous Entry In Query Loop
              wcx08 Level 1

              Actually, I think I just found a way to do it with the "cfloop query". 

               

              <cfloop query="qryData">

                   #data1#

                   #qryData.data2[currentRow + 1]#

              </cfloop>

               

              The #currentRow# variable is very useful here.  You can add any integer to it to go up and down the rows in the query.

               

              I guess I answered this thread for myself!

               

              But thank you very much for the input anyways! 

              • 4. Re: Referring to Previous Entry In Query Loop
                wcx08 Level 1

                Hmm..Your way has a slightly different syntax than mine.  Any specifics I should know about?

                 

                Thanks!

                • 5. Re: Referring to Previous Entry In Query Loop
                  Reed Powell Level 3

                  You need some CFIFs in there to test for current row = 1 when looking at previous rows, or currentrow = recordcount when looking at following rows, else you'll get some errors.

                  -reed

                  • 6. Re: Referring to Previous Entry In Query Loop
                    ilssac Level 5

                    wcx08 wrote:

                     

                    Hmm..Your way has a slightly different syntax than mine.  Any specifics I should know about?

                     

                    Thanks!

                     

                    1. I quallified which query recordset's currentRow value of which I was refering.  This really doesn't matter, until such a time as one is trying to make a complex relationship between two or more record sets, and you may be in the middle of looping through two or more of them.
                    2. I used a - 1 as you indicated you wanted the previous row in the set, not a + 1 which would be accessing the next row.
                    3. As mentioned a couple of times now.  You will want some logic around this to handle when you are either at the beginning OR the end of the record set, so that the - 1 OR + 1 does NOT step outside the possible values and thus throw an exception.