Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Trouble with CFLOOP and Creating a list

Posted on 2007-04-01
3
Medium Priority
?
240 Views
Last Modified: 2012-08-13
I cannot seem to be able to create a list of names from a list of id's. Keeps doubling the list up when all I want is one list.
I need "John,Sara,Jack,Jill"
I have "112,115,97,420"
I want to query the DB and get the names from the id and create a comma list like above, however they keep doubling based on my code below. Any idea how to fix this?

Here's the code
<cfset mylist = "112,115,97,420">
<cfset newemplist = "">
<cfloop index="newlist" list="#mylist#" delimiters=",">
      <cfquery name="getemp" datasource="DB">
            select * from employees where emp_id = #newlist#
      </cfquery>
      <cfoutput query="getemp">
            <cfif #newemp# eq "">
                  <cfset newemp = "#document#">
            <cfelse>
                  <cfset newemp = "#newdoc#,#document#">
            </cfif>
      </cfoutput>

</cfloop>

It creates John John,Sara, John John, Sara, John, Sara, Jack, John John, Sara, John John, Sara,Jill..... Or something like that.  Any ideas would be great! Thanks
0
Comment
Question by:dudeatwork
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 53

Expert Comment

by:Ryan Chong
ID: 18832270
try something like this (you need to customize a bit accordingly):

<cfset mylist = "112,115,97,420">
<cfset newemplist = "">

<cfquery name="getemp" datasource="DB">
            select * from employees where emp_id in ( #newlist# )
      </cfquery>

<cfloop query="getemp">
          <cfif newemplist = "">
                <cfset newemplist = #getemp.yourFieldName#>
          <cfelse>
                <cfset newemplist = newemplist & ", " & #getemp.yourFieldName#>          
          </cfif>
</cfloop>

<cfoutput>Name list = #newemplist#</cfoutput>


hope this helps
0
 
LVL 39

Expert Comment

by:gdemaria
ID: 18834014
<cfset newemp = "#newdoc#,#document#">
I don't see where NewDoc and Document are coming from?


What you want to do is append the field name to your list variable each time..

<cfset newEmp = "">
<cfoutput query="getemp">
   <cfset newEmp = listAppend(newEmp, document)>
</cfoutput>
0
 
LVL 39

Accepted Solution

by:
gdemaria earned 2000 total points
ID: 18836095

 Here's another way to do it that will keep you from having to run a query again and again within the loop.

 Just fetch all employess who's ID is on the list and use that query to create your ID and your name list.   Its important to recreate your ID list!!!  Because the order of the names fetched may not match the order of the IDs in your original list (so the names would be out of order).  But if you just create the ID list again, you'll be all set.

<cfset mylist = "112,115,97,420">

<cfquery name="getemp" datasource="DB">
  select empID, firstName
  from employees where emp_id in (#myList#)
  order by firstName -- you can change the order of the list here
</cfquery>
<cfset empNames = valueList(getEmp.firstName)>
<cfset empIDs = valueList(getEmp.empID)>  <!--- create ID list as well !! ---->

0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
Please read the paragraph below before following the instructions in the video — there are important caveats in the paragraph that I did not mention in the video. If your PaperPort 12 or PaperPort 14 is failing to start, or crashing, or hanging, …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

609 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question