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.
source to share