search screen query logic CFIF
I am building a search screen and the search items are
COUNTY, CITY, ZIP, NAME. I would like to be able to
search on each item independent of the other and I
would like to be able to search on the COUNTY and CITY
using AND.
I have two select boxes populated by the database
COUNTY and CITY. When you first enter the screen the
CITY list all cities. When the COUNTY is selected the
CITY drop down is populated by the cities that are in
that county only. My problem is the query logic isn't
working. The only thing that is working is if the
COUNTY and CITY is both selected. If the COUNTY is
selected without the city my <cfelse> doesn't produce
any results and if I select the CITY without the COUNTY
that isn't working either.
When I add in the other search criteria (zip, name) it
isn't working either.
-----form page-----
<form name="fPharmacy" action="results.cfm" method="post">
<SELECT NAME="county" onChange="JavaScript:window.location.href='PharmDirectory.cfm?selcounty='+document.fPharmacy.county.value">
<option value=""></option>
<CFOUTPUT QUERY="County">
<OPTION VALUE="#County.county#" <cfif isDefined("selcounty")><cfif County.county EQ selcounty>Selected</cfif></cfif>>#County.county#
</CFOUTPUT>
</SELECT>
<SELECT NAME="city">
<option value=""> </option>
<CFOUTPUT QUERY="qCity">
<OPTION VALUE="#qCity.city#">#qCity.city#
</CFOUTPUT>
</SELECT>
<input name="zip" type="text" size="10">
<input name="name" type="text" size="40">
<input name="search" type="SUBMIT" VALUE="Search" class="BUTTON">
</form>
Results page----------
<CFQUERY DATASOURCE="#MESSADSN#" NAME="Pharmacy">
SELECT DISTINCT name, address, city, state, zip, county, phone
FROM pharmacy
<cfif (IsDefined("county"))>
<cfif (IsDefined("city"))>
where county = '#trim(county)#' AND city ='#trim(city)#'
<cfelse>
where county = '#trim(county)#'
</cfif></cfif>
<cfif county NEQ "">
where county = '#trim(county)#'
</cfif>
<cfif city NEQ "">
where city = '#trim(city)#'
</cfif>
<cfif name NEQ "">
where name like '#trim(name)#%'
</cfif>
ORDER BY name
</CFQUERY>
Cathy
I am building a search screen and the search items are
COUNTY, CITY, ZIP, NAME. I would like to be able to
search on each item independent of the other and I
would like to be able to search on the COUNTY and CITY
using AND.
I have two select boxes populated by the database
COUNTY and CITY. When you first enter the screen the
CITY list all cities. When the COUNTY is selected the
CITY drop down is populated by the cities that are in
that county only. My problem is the query logic isn't
working. The only thing that is working is if the
COUNTY and CITY is both selected. If the COUNTY is
selected without the city my <cfelse> doesn't produce
any results and if I select the CITY without the COUNTY
that isn't working either.
When I add in the other search criteria (zip, name) it
isn't working either.
-----form page-----
<form name="fPharmacy" action="results.cfm" method="post">
<SELECT NAME="county" onChange="JavaScript:window.location.href='PharmDirectory.cfm?selcounty='+document.fPharmacy.county.value">
<option value=""></option>
<CFOUTPUT QUERY="County">
<OPTION VALUE="#County.county#" <cfif isDefined("selcounty")><cfif County.county EQ selcounty>Selected</cfif></cfif>>#County.county#
</CFOUTPUT>
</SELECT>
<SELECT NAME="city">
<option value=""> </option>
<CFOUTPUT QUERY="qCity">
<OPTION VALUE="#qCity.city#">#qCity.city#
</CFOUTPUT>
</SELECT>
<input name="zip" type="text" size="10">
<input name="name" type="text" size="40">
<input name="search" type="SUBMIT" VALUE="Search" class="BUTTON">
</form>
Results page----------
<CFQUERY DATASOURCE="#MESSADSN#" NAME="Pharmacy">
SELECT DISTINCT name, address, city, state, zip, county, phone
FROM pharmacy
<cfif (IsDefined("county"))>
<cfif (IsDefined("city"))>
where county = '#trim(county)#' AND city ='#trim(city)#'
<cfelse>
where county = '#trim(county)#'
</cfif></cfif>
<cfif county NEQ "">
where county = '#trim(county)#'
</cfif>
<cfif city NEQ "">
where city = '#trim(city)#'
</cfif>
<cfif name NEQ "">
where name like '#trim(name)#%'
</cfif>
ORDER BY name
</CFQUERY>
Cathy