I am going nuts trying to figure out what I am sure is a simple mistake with
this query. I am trying to loop through some form data coming as a list of
numbers and letters (school grade levels). The data in the table is also a
comma-delimited list. So, the form data might come back as "K, 1, 2", and I
need to find any records in the database where ANY of "K, 1, 2" are included.
I keep getting no records returned, when I know there are plenty that meet the
criteria. What am I missing? Thanks in advance
<cfif IsDefined("form.gradelevels")>
<cfset gradelevels = '#form.gradelevels#'>
<cfelse>
<cfset gradelevels = 'Pre-K,K,1,2,3,4,5,6,7,8,9,10,11,12'>
</cfif>
<cfquery name="gradesearch">
SELECT DISTINCT * from books
WHERE 0=0
<cfloop index="i" list="#gradelevels#" delimiters=", ">
AND (gradelevel LIKE '#i#,%'
OR gradelevel LIKE '%,#i#,'
OR gradelevel LIKE '#i#,%'
OR gradelevel = '#i#')
</cfloop>
</cfquery>
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~|
Want to reach the ColdFusion community with something they want? Let them know
on the House of Fusion mailing lists
Archive:
http://www.houseoffusion.com/groups/cf-newbie/message.cfm/messageid:4816
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
