Skip to main content
M
mega_L
Inspiring
August 11, 2010
Question

Help with creating a list

  • August 11, 2010
  • 2 replies
  • 840 views

I query id values from one of my table.

I need to come up with a list that looks like:

id='123' OR id='234' OR id='002' OR id='345' OR id='435'

I came up with 123,234,002,345,435 and '123' , '234' , '002' , '435' and also '123' OR '234' OR '002' OR '435'

but very frustrated with putting id= on each list element to come up with id='123' OR id='234' OR id='002' OR id='345' OR id='435'

Can anyone think of a technique to create such a list dynamically from a query result?

Thanks!

This topic has been closed for replies.

2 replies

M
mega_LAuthor
Inspiring
August 11, 2010

I think I'm not too clear with my plea for help.

I'm not going to use the list in my query. I need to create this list so I can send it through web services to get something back.

I was told that the web services does not take in id IN ('123','234','002','354') but it takes in id='123' OR id='324' OR id='435' OR etc

J
JR__Bob__Dobbs
Inspiring
August 11, 2010

You could create a user defined function that converts a comma delimited list of values into one formatted with id's and OR's.

Here is a quick sample which has NOT been tested.

<cffunction name="customFormatOrList" returntype="string" output="no" hint="Converts comma delimited list to OR query string">
    <cfargument name="inputList" type="string" required="yes" hint="Comma delimited list" />


    <cfset var local=StructNew() />

    <cfset local.resultString="" />

    <cfloop list="#arguments.inputList#" index="local.idx">

        <!--- add 'OR' if this is not the first item --->
        <cfif Len(local.resultString) gt 0>
            <cfset local.resultString="#local.resultString# OR " />
        </cfif>

        <cfset local.resultString="#local.resultString#id='#local.idx#'" />

    </cfloop>

    <cfreturn local.resultString />

</cffunction>

M
mega_LAuthor
Inspiring
August 11, 2010

Thank you!! I tested and it works. I just needed to tweak my list a bit.

J
JR__Bob__Dobbs
Inspiring
August 11, 2010

Use CFQUERYPARAM with the list attribute in your query.

<cfset myList="A100,B200,C300">

<cfquery name="myQuery">

     SELECT *

     FROM myTable

     WHERE idKey IN (  <cfqueryparam cfsqltype="cf_sql_char" list="yes" value="#myList#">)

</cfquery>

D
Dan_Bracuk
Inspiring
August 11, 2010

Depending on where the data is coming from, you might be able to achieve the same thing by having a single query that selects from more than one table.

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices