Solved

Trouble with CFLOOP and Creating a list

Posted on 2007-04-01
3
225 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
  • 2
3 Comments
 
LVL 51

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 500 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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone 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

PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

679 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