Clever Geek Handbook

Is there an elegant way to dynamically generate a query based on list values?

I created this bump of code that works and does what I want, but I was wondering if there is a better way to do this. Here is the code:

<cfset myList = ValueList(qSeminarWisReg.SeminarWisTitle,",")>

<cfif ListLen(myList,",") lte 1>
  <cfquery datasource="#APPLICATION.dataSource#" name="qSeminarWisRegTwo">
  SELECT * FROM SeminarWis
  WHERE SeminarWisID = <cfqueryparam value="#myList#" cfsqltype="cf_sql_integer">
  </cfquery>
<cfelse>
  <cfquery datasource="#APPLICATION.dataSource#" name="qSeminarWisRegTwo">
  SELECT * FROM SeminarWis
  WHERE 0 = 0
    AND (SeminarWisID  = <cfqueryparam value="100000000" cfsqltype="cf_sql_integer">
    <cfloop index = "x" list = "#myList#" delimiters = ","> OR SeminarWisID  = #x#</cfloop>)
  </cfquery>
</cfif>

Basically, I have a dynamically generated list that has comma separated numbers. These numbers now correspond to unique keys in the table, so I just dynamically grab all the rows the user wants.

As I said, it works, but if anyone has any advice on how to do it better, let me know! The part that bothers me is that I have to use the value "1000000" (which doesn't exist) before I can enter the OR statements.

+3


source to share


1 answer


I think you want to use CFQUERYPARAM with list = "true" and IN operator:

SELECT * 
FROM   SeminarWis
WHERE  SeminarWisID IN ( 
          <cfqueryparam value="#myList#" cfsqltype="cf_sql_integer" list="true" /> 
       )


Please note that this will only work if listLen (myList) GTE 1

+10


source






More articles:


All Articles