Solved

Coldfusion Search forms: how to disable case sensitivity

Posted on 2008-10-01
5
751 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 500 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 500 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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

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…
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 …
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Edureka is one of the fastest growing and most effective online learning sites.  We are here to help you succeed.

911 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

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now