asked on

Return List of Ids

Hello Experts-

I have a list of names that I am calling a db query to pull the IDs, but am having trouble getting the IDs into an actual list. It is returning 16 487 488 rather than 16,487,488

<cfset affiliateList = #form.id#><!--- Looks like.... C - DME Media,KC - Floor Coverings International - Google HL,KC - Floor Coverings International - Yahoo --->
    
     <cfloop list="#affiliateList#" index="i" delimiters=",">
        <cfquery name="getAffiliateID" dbtype="query">
            select affiliateID
            from getCampaigns
            where name IN ('#i#')
         </cfquery>
         
     	<cfoutput query="getAffiliateID">
        	<cfset finalList = valuelist(getAffiliateID.affiliateID,",")>
           <cfdump var="#finalList#"> 
         </cfoutput>
     </cfloop>

Open in new window

gdemaria

there is function for that...

No need for a loop, just put this right after your cfquery

<cfset finalList= valueList(getAffiliateID.affiliateID)>

nmarano

ASKER

Will do Maria...give a few to try it out

nmarano

ASKER

Hi Maria-

So this is what the query looks like that is trying to run.....

select affiliateID
from getCampaigns
where name IN ('KC - DME Media,KC - Floor Coverings International - Remarketing,KC - Floor Coverings International - Yahoo')

Rather than finding each of the names separated by comma(,) it's looking for this one string.

ASKER CERTIFIED SOLUTION

gdemaria

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

nmarano

ASKER

Thank you! Needed the listappend.

Thanks again Maria

_agx_

If the list of items isn't too long, another possibility is use a single query with <cfqueryparam list="true">

        <cfquery name="getAffiliateID" dbtype="query">
            select affiliateID
            from getCampaigns
            where name IN ( <cfqueryparam value="#affiliateList#" cfsqltype="cf_sql_varchar" list="true">) 
         </cfquery>
         <cfset finalList = valuelist(getAffiliateID.affiliateID,",")>

Open in new window