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

Posted on 2011-10-13
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:

* 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 defined? --->
<cfif IsDefined("")>

<!--- 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 defined? --->

Open in new window

Question by:Eric Bourland
    LVL 51

    Accepted Solution

    <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">

    LVL 3

    Author Comment

    by:Eric Bourland
    _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.

    LVL 3

    Author Closing Comment

    by:Eric Bourland
    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 51

    Expert Comment

    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

    Why You Should Analyze Threat Actor TTPs

    After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

    Join & Write a Comment

    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…
    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 video is in connection to the article "The case of a missing mobile phone (". It will help one to understand clearly the steps to track a lost android phone.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    746 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

    15 Experts available now in Live!

    Get 1:1 Help Now