USING Sql IN with CFQUERYPARAM the list attribute

I have a page that grabs a list of numbers, either from a series of checkboxes, or a multiple select box, and it passes the Value of the item…

<select name=“Seminars” multiple=“multiple” size=“10”>

<cfloop query=“somequery”>

<option value=“#seminarid#”>#title#</option>

</cfloop>

</select>

when you submit the page, and you select multiple items from the box, you’re going to get a list returned back in the form.seminars… ie. (1,2,3), here you could utilize the list attribute in cfqueryparam to work for you in trying to grab some rows from the database only where what was selected is relevant…

<cfquery name=“qGetSeminarsUsers” datasource=“#datadsn#”>

SELECT

su.userid, su.seminarid as id, su.fname, su.lname, su.email

FROM seminarusers su

WHERE su.seminarid IN

(

<cfqueryparam list=“yes”

cfsqltype=“cf_sql_integer”

value=“#arguments.seminars#”

/>

)

</cfquery>

as a begginer you may try to loop through the list in your query to grab all of the id’s and use a lot of “AND” “OR” sql statments in you’re WHERE clause. BUT using the “IN” clause in conjunction the the list=”yes” feature of cfqueryparam, it makes this query a breeze.

1 comment on this post.
  1. rb:

    Thanks for sharing!

    I just ran into this problem, and I’m sure I would have solved it by reading the adobe livedocs, but this was the first result on my google search!

    thanks again

Leave a comment