If you have a data in table seperated by comma, so you can treat it as a
list in CF:
**************************************
<cfouput query="gradesearch">
<cfset lst_gradelevels = gradelevels />
<cfif ListFindNoCase(lst_gradelevels,'K')>
....do smth for K
</cfif>
<cfif ListFindNoCase(lst_gradelevels,'Pre-K')> ....do
smth for Pre-K
</cfif>
OR it can be done by using cfswitch wich works faster then IF
statement
<cfloop index="i" list="#gradelevels#">
<cfswitch expression="#i#">
<cfcase value="K">
....do smth for K
</cfcase>
<cfcase value="Pre-K"> ..... do smth for Pre-K
</cfcase>
etc.....
</cfswitch>
</cfloop>
</cfouput>
**************************************
But I would agree with prior comments, seems you have not well normalized
data in DB.
Regards,
Misha
On Wed, Sep 23, 2009 at 12:00 PM, Debi Lewis <[email protected]> wrote:
>
> 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:4819
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe:
http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=11502.10531.15
