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

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.

Eric


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
</cfquery>

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

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

<!--- 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
</cfquery>

 <!--- BEGIN HTML / CSS PAGE HEADER --->
<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>
     </cfselect></p>


<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>
          </cfselect></p>
</div>

       
<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>
     </cfselect></p>
</div>

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

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

</cfform>

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">
SELECT
cp.CareplannersUUID
,cp.CareplannersID
,prefix
,firstname
,middleinit
,lastname
,suffix
,company
,primaryphone
,UserEmail
,website
,street1
,city1
,state1
,zip1
,country1
,phone1
,fax1
,street2
,city2
,state2
,zip2
,country2
,phone2
,fax2
,practice
,info

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

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

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

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

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

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

<cfif keyword IS NOT "">
AND (
 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">
)
</cfif> 

ORDER BY  LastName ASC
</cfquery>

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

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

 <!--- BEGIN HTML / CSS PAGE HEADER --->
<cfinclude template="/Siteheader.cfm">


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

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

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

<!--- 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>
#firstname#
<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>

</div>
<!--- /display_left --->


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

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


<!--- /display_right --->
</div>

<!--- end careplanners display --->
</div>

</cfoutput>
 


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

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

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

Open in new window

LVL 3
Eric BourlandAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

_agx_Commented:
<cfif state1 IS NOT "">
AND state1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.state1#" maxlength="50">
</cfif>
I think you can change your condition to search both columns. The parenthesis are very important


<cfif state1 IS NOT "">
AND
(
      state1 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.state1#" maxlength="50">
      OR
      state2 = <cfqueryparam cfsqltype="cf_sql_varchar" value="#form.state1#" maxlength="50">
)
</cfif>

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Eric BourlandAuthor Commented:
_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.

brb
0
Eric BourlandAuthor Commented:
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.)

Eric
0
_agx_Commented:
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 :)  

-Later!

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ColdFusion Language

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.