I would highly recommend you normalize this data out into its own table and then you can make your life really easy with a JOIN, BTW.
Mike -----Original Message----- From: Debi Lewis [mailto:[email protected]] Sent: Wednesday, September 23, 2009 11:00 AM To: cf-newbie Subject: CFLOOP and a "like" statement in query 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:4818 Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
