How can a search on a value for "state" list results from two columns: state1 and state2?

Posted on 2011-10-13
Medium Priority
Last Modified: 2012-05-12
How can a search on a value for "state" list results from two columns: state1 and state2?

* I have a new complication with my careplanners search tool: http://careplanners.net/pages/Search-LCP-Forum.cfm

* Some careplanners have offices in two states: state1 and state2. These careplanners are dissatisfied with the search tool because the search tool searches for, and lists, only state1.

* Example: Careplanner Robert has offices in state1 = Arizona; and state2 = Colorado. If someone searches for careplanners in Arizona, Robert's Colorado office does not appear in the result, because the state search lists only state1 (Arizona).

* Robert's point: people overlook his business if they search on state Colorado. Robert’s first business address (state1) is Arizona. His second address (state2) is Colorado. If people search on state Arizona, they find Robert. If they search on state Colorado, they do not find Robert.

I have been staring at this for a while and am not sure how to include state2 in the search results. I would like to keep the search interface simple, with only one dropdown select list of states.

How can a search on a value for "state" list results from two columns: state1 and state2?

I know this can be done -- I am not sure what syntax to use. Thank you for any advice.


Search interface:

Name:        search.cfm
Author:      Eric Bourland, gdemaria, _agx_
Description: search interface for Careplanners
Created:     September - October 2011
ColdFusion Version 9
MS SQL Server 2005

<!--- this query selects DegreeTitle from lookup table careplanners_degrees --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetDegrees">
        SELECT DegreeID, DegreeTitle
        FROM careplanners_degrees

<!--- this query selects certificationTitle from lookup table careplanners_certifications --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetCertifications">
        SELECT certificationID, certificationTitle
        FROM careplanners_certifications

 <!--- this query selects practice from lookup table careplanners_practice --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetPractice">
        SELECT practice
        FROM careplanners_practice
        ORDER BY practice ASC

<!--- this query selects StateName values from lookup table USCA_States --->
<cfquery datasource="#APPLICATION.dataSource#" name="GetStates">
        SELECT StateName
        FROM USCA_States
        ORDER BY StateName ASC

<cfinclude template="/Siteheader.cfm">

<h2>Search Life Careplanners</h2>
<p><strong>Search by:</strong></p>

<cfform action="/searchquery.cfm?search=Yes" method="post" name="searchCareplanners" style="margin-top:1px;">

<div class="width300px float-left">

<p>Last Name: <cfinput type="text" name="LastName" size="20" /></p>

<p>Practice: <cfselect size="1" name="practice" value="practice" display="practice" multiple="no" query="GetPractice" queryPosition="below">
     <option value="">Practice:</option>

<p>Degree: <cfselect size="1" name="DegreeID" value="DegreeID" display="DegreeTitle" multiple="no" query="GetDegrees" queryPosition="below">
                <option value="">Degree:</option>
          </cfselect> </p>

<p>Certification: <cfselect size="1" name="certificationID" value="certificationID" display="certificationTitle" multiple="no" query="GetCertifications" queryPosition="below">
                <option value="">Certification:</option>

<div class="width300px float-left">       

<p>Keyword: <cfinput type="text" name="keyword" size="20" /></p>


<p>City: <cfinput type="text" name="city1" size="20" /></p>

<p>State: <cfselect size="1" name="state1" value="StateName" display="StateName" multiple="no" query="GetStates" queryPosition="below">
     <option value="">Select State:</option>

<div class="clear-both"></div>

<div class="align-center">
  <input type="submit" value="Search Careplanners" />


Open in new window

Search results / display page:

Name:        searchquery.cfm
Author:      Eric Bourland, gdemaria, _agx_
Description: search results display page for Careplanners search
Created:     September - October 2011
ColdFusion Version 9
MS SQL Server 2005

<cfset bgcolor="##ffffff">
<cfparam name="form.keyword" default="">
<cfparam name="form.LastName" default="">
<cfparam name="form.practice" default="">
<cfparam name="form.city1" default="">
<cfparam name="form.state1" default="">
<cfparam name="form.DegreeID" default="">
<cfparam name="form.certificationID" default="">

<!--- is URL.search defined? --->
<cfif IsDefined("URL.search")>

<!--- query searchCareplanners, select columns from table CareplannersMembers --->
<cfquery datasource="#application.datasource#" name="searchCareplanners">

,DegreeTitleList = substring((SELECT ( ', ' + d.DegreeTitle )
		FROM careplanners_has_degrees hd
		inner join careplanners_degrees d on hd.degreeID = d.degreeID
		WHERE hd.CareplannersID = cp.CareplannersID
		ORDER BY d.DegreeTitle ASC
		FOR XML PATH( '' )), 3, 1000 )

,certificationTitleList = substring((SELECT ( ', ' + c.certificationTitle )
        FROM careplanners_has_certifications hc
        inner join careplanners_certifications c on hc.certificationID = c.certificationID
        WHERE hc.CareplannersID = cp.CareplannersID
        ORDER BY c.certificationTitle ASC
        FOR XML PATH( '' )), 3, 1000 )

FROM CareplannersMembers cp

WHERE 1 = 1

<cfif val(form.degreeID)>
AND exists (select 1
from careplanners_has_degrees chd
where chd.CareplannersID = cp.CareplannersID
and chd.degreeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.degreeID#">)

<cfif val(form.certificationID)>
AND exists (select 1
from careplanners_has_certifications chc
where chc.CareplannersID = cp.CareplannersID
and chc.certificationID = <cfqueryparam cfsqltype="cf_sql_integer" value="#form.certificationID#">)

<cfif practice IS NOT "">
AND practice = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.practice#">

<cfif city1 IS NOT "">
AND city1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.city1#" maxlength="40">

<cfif state1 IS NOT "">
AND state1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.state1#" maxlength="50">

<cfif LastName IS NOT "">
AND LastName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.LastName#%" maxlength="50">

<cfif keyword IS NOT "">
 info LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">
OR LastName LIKE <cfqueryparam cfsqltype="cf_sql_varchar" value="%#form.keyword#%" maxlength="100">


<!--- check search recordcount --->
<cfif searchCareplanners.RecordCount GT 0>

<cfquery name="getDegreeTitles" datasource="#application.datasource#">
SELECT DegreeID, DegreeTitle
FROM careplanners_degrees
WHERE DegreeID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.DegreeID)#">

<cfquery name="getCertTitles" datasource="#application.datasource#">
SELECT CertificationID, CertificationTitle
FROM careplanners_certifications
WHERE CertificationID = <cfqueryparam cfsqltype="cf_sql_integer" value="#val(form.CertificationID)#">

<cfinclude template="/Siteheader.cfm">

<p>You searched on these parameters:</p>

<p><strong>Careplanner Name:</strong> <em>#form.LastName#</em><br />
<strong>Degree:</strong> <em>#getDegreeTitles.DegreeTitle#</em><br />
<strong>Certification:</strong> <em>#getCertTitles.CertificationTitle#</em><br />
<strong>Primary Practice:</strong> <em>#form.practice#</em><br />
<strong>City:</strong> <em>#form.city1#</em><br />
<strong>State:</strong> <em>#form.state1#</em><br />
<strong>Keyword:</strong> <em>#form.keyword#</em></p>

<p class="align-center">Your search found #searchCareplanners.RecordCount# result<cfif searchCareplanners.RecordCount GT 1>s</cfif>. Results are sorted by Careplanner Last Name, ascending A to Z.</p>

<cfoutput query="searchCareplanners" group="LastName" groupcasesensitive="no">

 <cfif bgColor neq "##ffffff">
    <cfset bgcolor="##ffffff">
    <cfset bgcolor="##f7f5f5">

<!--- begin careplanners display --->
<div class="contractor_display" style="background-color:#bgcolor#; margin-top:20px;">

<!--- begin display_left --->
<div class="contractor_display_left">
<p><strong><cfif prefix IS NOT "">#searchCareplanners.prefix#</cfif>
<cfif middleinit IS NOT "">#middleinit#</cfif>
#lastname#<cfif suffix IS NOT "">, #suffix#</cfif></strong><br />

Company or Organization: #company#<br />

Primary Practice: #practice#<br />

Primary Phone: #primaryphone#<br />

Email: #UserEmail#<br />

Web Address #website#<br />

Street: #street1#<br />
City: #city1#<br />
State: #state1#<br />
ZIP or Postal Code: #zip1#<br />
Country: #country1#<br />
Business Telephone 1: #phone1#<br />
Fax 1: #fax1#<br />
<cfif street2 IS NOT "">Business Address 2: #street2#<br /></cfif>
<cfif city2 IS NOT "">#city2#<br /></cfif>
<cfif state2 IS NOT "">#state2#<br /></cfif>
<cfif zip2 IS NOT "">#zip2#<br /></cfif>
<cfif country2 IS NOT "">#country2#<br /></cfif>
<cfif phone2 IS NOT "">#phone2#<br /></cfif>
<cfif fax2 IS NOT "">Fax 2: #fax2#<br /></cfif>
<cfif info IS NOT "">Information: #info#</cfif></p>

<!--- /display_left --->

<!--- begin display_right --->
<div class="contractor_display_right">
<p><strong>Degrees:</strong><br />
<cfoutput group="DegreeTitleList" groupcasesensitive="no">

<p><strong>Certifications:</strong><br />
<cfoutput group="certificationTitleList" groupcasesensitive="no">
</cfoutput> </p>

<!--- /display_right --->

<!--- end careplanners display --->


<p><strong>No items matched your query.</strong></p>
<p class="align-center"><a href="/directory.cfm">Return to Careplanners Directory</a></p>

</cfif><!--- /check search recordcount --->

</cfif><!--- /is URL.search defined? --->

Open in new window

Question by:Eric Bourland
  • 2
  • 2
LVL 52

Accepted Solution

_agx_ earned 2000 total points
ID: 36964284
<cfif state1 IS NOT "">
AND state1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.state1#" maxlength="50">
I think you can change your condition to search both columns. The parenthesis are very important

<cfif state1 IS NOT "">
      state1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.state1#" maxlength="50">
      state2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.state1#" maxlength="50">


Author Comment

by:Eric Bourland
ID: 36964308
_agx_, I tried exactly that. Did not seem to work -- I got no results at all. Hang on, I'll try it again, and confirm my result. That does look like the obvious answer, I agree with you.


Author Closing Comment

by:Eric Bourland
ID: 36964349
Um, you were right, that worked. =) That was exactly the right solution. I tried that solution, but obviously got something else wrong somewhere, that caused me to derive no results in any search. Hmm. But, this works, and i see why it works. Thank you as always to _agx_. That was a fast answer!  Hope you are having an excellent day. (Rainy and gray in Chicago.)

LVL 52

Expert Comment

ID: 36964394
Glad you're doing well. Yep, it's rainy and gray all around. Guess it's good for the plants (and I like those) .. so I can't complain :)  



Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

PROBLEM:  How to open a cfwindow or run a function on double click of a cfgrid row. One of my clients wanted to be able to double click on a row item to get more detailed information about a transaction and to be able to modify the line items i…
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 …
Whether it be Exchange Server Crash Issues, Dirty Shutdown Errors or Failed to mount error, Stellar Phoenix Mailbox Exchange Recovery has always got your back. With the help of its easy to understand user interface and 3 simple steps recovery proced…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
Suggested Courses
Course of the Month13 days, 8 hours left to enroll

750 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