Axelology
Posted At : November 4, 2006 7:29 PM
| Posted By : Axel Jensen
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>
<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>
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.
Blog provided and hosted by CF Webtools. Blog Sofware by Ray Camden.
There are no comments for this entry.
[Add Comment]