Skip to main content
T
TheScarecrow
Known Participant
November 10, 2011
Question

Query from an Array

  • November 10, 2011
  • 2 replies
  • 1253 views

if I hav an Array and I want to run a query with the array values as part of the where clause how does that work or can it? Thanks.

<cfset membership=ArrayNew(1)>

<cfset membership[1]="dog">

<cfset membership[2]="cat">

<cfset membership[3]="lizard">

<cfset membership[4]="fish">

<cfquery name="healthyliving" datasource="4H_projects">

          SELECT *

    FROM resource

    WHERE membership in <cfqueryparam cfsqltype="cf_sql_longvarchar" list="yes" value="#membership#"

</cfquery>

    This topic has been closed for replies.

    2 replies

    D
    Dan_Bracuk
    Inspiring
    November 10, 2011

    Even though you have an answer that works, there might be a more efficient way to handle this.  Where does the array come from in the first place?

    T
    TheScarecrowAuthor
    Known Participant
    November 10, 2011

    Its built into the page.  I created it.  They are the membership titles of files in my database.

    T
    TheScarecrowAuthor
    Known Participant
    November 11, 2011

    Can you add a second statement to it like

    (<cfqueryparam cfsqltype="cf_sql_varchar" list="yes" value="#ArrayToList(submembership)#" >)

    I
    insuractive
    Inspiring
    November 10, 2011

    You're halfway there with the use of the list="yes" attribute, however, you need to make sure that your variable is an actual list (delimeted string) and not an array:

    <cfset membership=ArrayNew(1)>

    <cfset membership[1]="dog">

    <cfset membership[2]="cat">

    <cfset membership[3]="lizard">

    <cfset membership[4]="fish">

    <cfquery name="healthyliving" datasource="4H_projects">

              SELECT *

        FROM resource

        WHERE membership in (<cfqueryparam cfsqltype="cf_sql_varchar" list="yes" value="#ArrayToList(membership)#" >)

    </cfquery>

    Try that and see if it helps.

    - Michael

    T
    TheScarecrowAuthor
    Known Participant
    November 10, 2011

    Awesome. Thank you so much.

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