Solved

having a little trouble with a find statement

Posted on 2014-01-16
4
186 Views
Last Modified: 2014-01-17
I'm converting some old code;

<cfif #find("Any US",USREGION)#>
	<cfset querystring = "#querystring#" & "(USREGION IS NOT NULL)" >
	<cfset USREGIONquery = "true">
<cfelse>
	<cfif #find("Carolinas",USREGION)#>
		<cfif USREGIONquery EQ "true">
			<!--- or (USREGION Like '%Carolinas%') --->
			<cfset querystring = "#querystring#" & " or (USREGION Like '%Carolinas%')" > 
		<cfelse>
			<!--- (USREGION Like '%Carolinas%') --->
			<cfset querystring = "#querystring#" & "(USREGION Like '%Carolinas%')" >
			<cfset USREGIONquery = "true">
		</cfif>
	</cfif>
	<cfif #find("Mountain West",USREGION)#>
		<cfif USREGIONquery EQ "true">
			<!--- or (USREGION Like '%Mountain West%') --->
			<cfset querystring = "#querystring#" & " or (USREGION Like '%Mountain West%')" > 
		<cfelse>
			<!--- (USREGION Like '%Mountain West%') --->
			<cfset querystring = "#querystring#" & "(USREGION Like '%Mountain West%')" >
			<cfset USREGIONquery = "true">
		</cfif>
	</cfif>    etc......

Open in new window



To a more dynamic table based code;

<cfinvoke component="services.jobboard" method="getRegions" returnvariable="regions" />
    <cfset USREGIONquery = "false">
    <cfset USREGION = "South">
    <cfset querystring = "(" >

    <cfoutput>
        <cfif #find("Any US",USREGION)#>
            <cfset querystring = "#querystring#" & "(USREGION IS NOT NULL)" >
            <cfset USREGIONquery = "true">
        <cfelse>
            <cfloop query="regions">
                #usregion#-#regions.description#<br>
                #querystring#<br>
                <cfif #find(regions.description,USREGION)#>
                    <cfif USREGIONquery EQ "true">
                        <cfset querystring = "#querystring#" & " or (USREGION Like '%#regions.description#%')" >
                #querystring#<br>
                    <cfelse>
                        <cfset querystring = "#querystring#" & "(USREGION Like '%#regions.description#%')" >
                #querystring#<br>
                        <cfset USREGIONquery = "true">
                    </cfif>
                </cfif>
            </cfloop>
            <cfif USREGIONquery EQ "true">
                <cfset querystring = "#querystring#" & " or (USREGION Like '%All%')" >
                #querystring#<br>
            </cfif>
            <cfif querystring eq "(">
                <cfset querystring = "" >
            <cfelse>
                <cfset querystring = "#querystring#" & ")" >
            </cfif>
            #querystring# 
        </cfif>
    </cfoutput>

Open in new window


The problem is the find function doesn't seem to be working.  I've tried many variations.  The output I get is;

South-New England
(South-Northeast
(South-Mid Atlantic
(South-Southeast
(South-South
(South-Great Lakes
(South-Midwest
(South-South Central
(South-Mountain States
(South-Southern California
(South-Northern California
(South-Southwest
(South-Northwest
(
0
Comment
Question by:lanterv
  • 2
  • 2
4 Comments
 
LVL 12

Accepted Solution

by:
mkishline earned 500 total points
ID: 39788863
Couple of thoughts. The first would be to switch your "Find" to "FindNoCase" which will make the search case-insensitive. You may also want to consider doing the same thing for the conditions in your query.

For example:
<cfset querystring = "#querystring# OR (USREGION LIKE '%#regions.description#%') />

Open in new window


Would become:
<cfset querystring = "#querystring# OR (LOWER(USREGION) LIKE '%#LCase(regions.description)#%') />

Open in new window


It may have been lost during your post, but it appears that you're also missing an operator in your <cfelse> block (see below)

<cfinvoke component="services.jobboard" method="getRegions" returnvariable="regions" />
<cfset USREGIONquery = "false">
<cfset USREGION = "South">
<cfset querystring = "" >

<cfoutput>
	<cfif FindNoCase("Any US",USREGION)>
		<cfset querystring = "#querystring# (USREGION IS NOT NULL)" >
		<cfset USREGIONquery = "true">
	<cfelse>
		<cfloop query="regions">
			#usregion#-#regions.description#<br>
			#querystring#<br>
			<cfif FindNoCase(regions.description,USREGION)>
				<cfif USREGIONquery EQ "true">
					<cfset querystring = "#querystring# or (USREGION Like '%#regions.description#%')" >
			#querystring#<br>
				<cfelse>
					<!--- Should have "AND, OR, or some other operator between #querystring# and "(" --->
					<cfset querystring = "#querystring# (USREGION Like '%#regions.description#%')" >
			#querystring#<br>
					<cfset USREGIONquery = "true">
				</cfif>
			</cfif>
		</cfloop>
		<cfif USREGIONquery EQ "true">
			<cfset querystring = "#querystring# or (USREGION Like '%All%')" >
			#querystring#<br>
		</cfif>
		<cfif querystring NEQ "">
			<cfset querystring = "(#querystring#)" />
		</cfif>
		#querystring# 
	</cfif>
</cfoutput>

Open in new window

0
 

Author Comment

by:lanterv
ID: 39788926
<cfif USREGIONquery EQ "true">
means that the query string has already been added to and an and/or is needed.

The findnocase issue is a good one, but the original code didn't use it and worked fine.  I have made the change anyway but it didn't help.
0
 
LVL 12

Expert Comment

by:mkishline
ID: 39788999
In the list of the output that you posted:
South-New England
(South-Northeast
(South-Mid Atlantic
(South-Southeast
(South-South
(South-Great Lakes
(South-Midwest
(South-South Central
(South-Mountain States
(South-Southern California
(South-Northern California
(South-Southwest
(South-Northwest

Is the idea that the desired list should only contain the records whose regions.description contains "South"?
Desired resultset:
South-Southeast, South-South, South-South Central, South-Southern California, South-Southwest

If so, you should switch the arguments you are sending to the find function:
<cfif Find(USREGION, regions.description)>

Open in new window


If I have misidentified what your expected result set should look like, can you provide more detail as to what your desired results would contain?
0
 

Author Comment

by:lanterv
ID: 39789000
I haven't figured out why yet, but the code is working now with the findnocase changes.  Thank you.
0

Featured Post

DevOps Toolchain Recommendations

Read this Gartner Research Note and discover how your IT organization can automate and optimize DevOps processes using a toolchain architecture.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The technique is by far very Simple! How we can export the ColdFusion query results to DOC file?  Well before writing this I researched a lot in Internet but did not found a good Answer anyways!  So i thought now i should share my small snippet w…
This is an updated version of a post made on my blog over 3 years ago. It is unfortunately, still very relevant as we continue to see both SQLi (SQL injection) and XSS (cross site scripting) attacks hitting some of the most recognizable website and …
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…

777 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question