CFLOOP and a "like" statement in query

[Debi Lewis]

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=17837.14401.15

[Mike Chytracek]

One thing I see is your delimiter is set to ", " when it should be ",".  No
Space.





-----Original Message-----
From: Debi Lewis [mailto:debilewis@...]
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:4817
Subscription: http://www.houseoffusion.com/groups/cf-newbie/subscribe.cfm
Unsubscribe: http://www.houseoffusion.com/cf_lists/unsubscribe.cfm?user=17837.14401.15

[Mike Chytracek]

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:debilewis@...]
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=17837.14401.15

[Misha Mishyn]

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 <debilewis@...> 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=17837.14401.15