Solved

Coldfusion Search forms: how to disable case sensitivity

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

Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

Join & Write a Comment

Suggested Solutions

Title # Comments Views Activity
Drop-down menu question 3 77
ColdFusion Report Builder 7 56
problem with finding cfcomponent on cfselect bind 7 63
cfchart display 12 91
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…
PROBLEM: How to add your own buttons to the bottom toolbar with paging info ( result count ). While creating a cfgrid, I ran into an issue where I wanted to embed my own custom buttons where the default ones ( insert / delete / etc… ) are for aes…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.

760 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

21 Experts available now in Live!

Get 1:1 Help Now