7 Replies Latest reply on Dec 7, 2011 5:17 AM by -==cfSearching==-

    Adding multiple db columns to ValueList()?

    lovewebdev Level 1

      I'm looping over a query inside cfscript. I need to place the results inside of another loop so I'm storing the results of the first query inside of a value list and then displaying that value list inside the second query results.

       

      The only problem is I need to put more than one db column side by side in the value list

       

       

      My code below...I need to add more columns beside the fname from the getstadd query. Is this even possible?

       

      ...

      for (
      intRow = 1 ;
      intRow LTE getstaff.RecordCount ;
      intRow = (intRow + 1)
      )
      
      { }
      
      var allstaff = "<span class='staffoncal'>" & ValueList(getstaff.fname, "<br />") & "</span>";
      
      
      outString = outString & "height='#height#'>" & allstaff & "<p />" & allevents & "</td> ";
      
        • 1. Re: Adding multiple db columns to ValueList()?
          BKBK Adobe Community Professional & MVP

          Valuelist is a function of just one column. As far as I know, you cannot apply it to multiple columns simultaneously.

           

          Anyway, converting from the valuelist to an array will give you more freedom. You will then have individual arrays which you can concatenate, style or display in any way you want. Here follows an example

           

          // array to hold list of first names

          var fnames = arrayNew(1);

           

          // array to hold list of corresponding last names

          var lnames = arrayNew(1);

           

          // initialize the arrays

          var isSet = arraySet(fnames, 1,getstaff.recordCount, "")>

          var isSet = arraySet(lnames, 1,getstaff.recordCount, "")>

           

          // set the array entries

          fnames = listToArray(valueList(getstaff.fname));

          lnames = listToArray(valueList(getstaff.lname));

           

          // concatenate fname and lname into full name and display

          for (

          intRow = 1 ;

          intRow LTE getstaff.RecordCount ;

          intRow = (intRow + 1)

          )

           

          {

              writeoutput("<span class='staffoncal'>" & fnames[intRow] & " " & lnames[intRow] & "<br /></span>");

          }

          • 2. Re: Adding multiple db columns to ValueList()?
            lovewebdev Level 1

            the problem is I can't really display the loop results right here:

             

            {

                writeoutput("<span class='staffoncal'>" & fnames[intRow] & " " & lnames[intRow] & "<br /></span>");

            }

             

             

            I need to store those looped results in a variable and concatenate it to another variable later in the page. Here particularly, next to or with the allstaff var:

             

             

             

            var allstaff = "<span class='staffoncal'>" & ValueList(getstaff.fname, "<br />") & "</span>";

             

            outString = outString & "height='#height#'>" & allstaff & "<p /></td> ";

            • 3. Re: Adding multiple db columns to ValueList()?
              -==cfSearching==- Level 4

              If you want to save the results to a variable, simply concatenate the values inside your for loop instead.  But there is no need to create extra arrays. Just access the query columns with array notation:

               

              yourVar = "";

              for (intRow = 1 ;intRow <= getstaff.RecordCount ;intRow = intRow + 1)

              {

                  yourVar = yourVar & getStaff.fnames[intRow] & " " & getStaff.lnames[intRow] & "....");

              }

              • 4. Re: Adding multiple db columns to ValueList()?
                BKBK Adobe Community Professional & MVP

                lovewebdev wrote:

                 

                the problem is I can't really display the loop results right here:

                 

                {

                    writeoutput("<span class='staffoncal'>" & fnames[intRow] & " " & lnames[intRow] & "<br /></span>");

                }

                 

                 

                I need to store those looped results in a variable and concatenate it to another variable later in the page. Here particularly, next to or with the allstaff var:

                 

                 

                 

                var allstaff = "<span class='staffoncal'>" & ValueList(getstaff.fname, "<br />") & "</span>";

                 

                outString = outString & "height='#height#'>" & allstaff & "<p /></td> ";

                There you are. That's what I mean when I say that doing it this way allows you some freedom.

                • 5. Re: Adding multiple db columns to ValueList()?
                  Dan Bracuk Level 5

                  Add the concatonated fields to your query.

                  1 person found this helpful
                  • 6. Re: Adding multiple db columns to ValueList()?
                    lovewebdev Level 1

                    Awesome. I think I got it now.

                     

                    I was having problems because I used getStaff.fname as opposed getStaff.fname[intRow] when concatenated...

                     

                    [intRow] was correct so what's the difference? I'm kind of new to cfscript and it looks funky. I'm so used to tags...

                    • 7. Re: Adding multiple db columns to ValueList()?
                      -==cfSearching==- Level 4

                      getStaff.fname[intRow] retrieves the value from a specific row in the query. Unless you are within a query loop, getStaff.fname retrieves the value in the first row of the query.