Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Coldfusion Search forms: how to disable case sensitivity

Posted on 2008-10-01
5
Medium Priority
?
790 Views
Last Modified: 2012-06-22
I'm using coldfusion to search and display data from an Oracle DB.

I've included the code snippet for my search form, its a basic text input search facility and it works pretty well for what I need.

However.....

Some of the fields in my Tables are upper case, and some are lower case and my search form is using a case sensitive approach to finding the data.

Is there a way to turn off case sensitivity?

I also need to display an error if the search returns no results.

thanks in advance.
<cfparam name="FORM.search_field" default="">
 
 
 
 
<cfquery name="Qsearch" datasource="wprop">
SELECT *
FROM contacts
WHERE FIRST_NAME LIKE '%#FORM.search_field#%' OR LAST_NAME LIKE '%#FORM.search_field#%' OR JOB_TITLE LIKE '%#FORM.search_field#%' OR DEPARTMENT LIKE '%#FORM.search_field#%'
ORDER BY LAST_NAME
</cfquery>
 
 
 
<!--  db search form -->
<cfform method="post" action="index.cfm">
<cfinput type="text" name="search_field" required="yes" message="Please enter a search" size="40">
<input name="action" id="action" type="submit">
</cfform>
 
.....
 
 
 
 
<cfif isDefined("action") is "true">
 
	<cfif trim(FORM.search_field) is "">
		<cfoutput>Please enter a search query</cfoutput>
        
        <cfelse>
        
        <cfloop query="Qsearch">
        <cfoutput>
        	<div><p>#Qsearch.LAST_NAME#, #Qsearch.FIRST_NAME#<br />
            #Qsearch.DEPARTMENT# - 
            #Qsearch.JOB_TITLE#</p></div>
         </cfoutput>
        </cfloop>
    </cfif>
 
</cfif>
 
.....

Open in new window

0
Comment
Question by:CurtinProp
  • 3
  • 2
5 Comments
 
LVL 27

Accepted Solution

by:
azadisaryev earned 2000 total points
ID: 22612408
one approach will be to convert all to one case. most DBs, and i am sure Oracle as well, have string manipulation functions like LOWER() and UPPER(), and CF has LCase() and UCase() functions. So you can do something like:

<cfquery name="Qsearch" datasource="wprop">
SELECT *
FROM contacts
WHERE LOWER(FIRST_NAME) LIKE '%#LCASE(trim(FORM.search_field))#%' OR ...
</cfquery>

check your db manual for proper function to use.
and please please please use <cfqueryparam> in your queries!!!

hth
0
 
LVL 27

Assisted Solution

by:azadisaryev
azadisaryev earned 2000 total points
ID: 22612422
on the error subject, and a few other things:
a) scope your variables: <cfif isDefined("action") is "true"> should really be <cfif isDefined("FORM.action")>
b) i would move your query inside this cfif block - no need to run it if the form was not submitted
c) no need for <cfloop>+<cfoutput> - just use <cfoutput query="Qsearch">
d) enclose your query output into another cfif block checking if your search query returnd any records and show error if not:
<cfif Qsearch.recordcount><cfoutput query="Qsearch">...</cfoutput><cfelse>Sorry, no results were found to match your search criteria... Please try again.</cfif>
d) did i mention you should use <cfqueryparam> in your queries???

hth
0
 
LVL 2

Author Comment

by:CurtinProp
ID: 22612447
hi, thanks for the quick response,

Im just going to test out your solutions now, why and where shoudl I use cfquery param?
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 22612471
if you want to protect yourself from SQL injection attacks and make your queries perform faster overall, use <cfqueryparam> on all values, user-submitted values especially. make it a rule that any value in your queries must be <cfqueryparam>'ed and you will not regret it.

usage is described in the CMFL reference manual. download free PDF from abobe.com if you do not have one.
0
 
LVL 2

Author Comment

by:CurtinProp
ID: 22621019
thanks for your help on this one, I've got a few more questions regarding cfquery param and the SQL for my search facility but I'll make them into a new question so you dont end up giving 12 solutions for nothing.

thanks again
0

Featured Post

[Webinar] Cloud and Mobile-First Strategy

Maybe you’ve fully adopted the cloud since the beginning. Or maybe you started with on-prem resources but are pursuing a “cloud and mobile first” strategy. Getting to that end state has its challenges. Discover how to build out a 100% cloud and mobile IT strategy in this webinar.

Question has a verified solution.

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

This article  is about submitting  form through  ColdFusion.Ajax.submitForm to the action page and send a response back in JSON format which later can be decoded using ColdFusion.JSON.decode. By this way you can avoid the usual page refresh for subm…
Sometimes databases have MILLIONS of records and we need a way to quickly query that table to return the results me need. Sure you could use CFQUERY but it takes too long when there are millions of records. That is why SOLR was invented. Please …
In response to a need for security and privacy, and to continue fostering an environment members can turn to for support, solutions, and education, Experts Exchange has created anonymous question capabilities. This new feature is available to our Pr…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Suggested Courses

916 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