search screen query logic CFIF

[msCrb]

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="">&nbsp;&nbsp;&nbsp;&nbsp;</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

 

[DonHawaii]

First things first. Have you made sure of what is going in to the query?

IOW have the results page just print out the data from the form. Possibly nothing is coming thru.

BUT there is a flaw in your logic.

<cfif (IsDefined("county"))>
<cfif (IsDefined("city"))>
where county = '#trim(county)#' AND city ='#trim(city)#'
<cfelse>
where county = '#trim(county)#'
</cfif>
</cfif>

I've done proper indentation here to show what matches what. The cfelse goes with the 2nd cfif. So IF county is defined AND city is defined, result is "where ....".
Else if city is NOT defined you just get where county = ...
This should work just fine.

BUT then it continues.

<cfif county NEQ "">

This is TRUE IF it is defined. So you already have gone through the first trap and have either where county = '#trim(county)#' AND city ='#trim(city)#' OR where county = '#trim(county)#'

where county = '#trim(county)#'

You have now added another where statement that is redundant

</cfif>
<cfif city NEQ "">

Once again you may have already had this line inserted

where city = '#trim(city)#'

</cfif>

What you really wanted to do was have a logic table like this:

<cfif ISDEFINED("form.county") AND form.county NEQ "">
where county = '#trim(county)#'
<cfif ISDEFINED("form.city") AND form.city NEQ "">
AND city = '#trim(city)#'
</cfif>
<cfelse>
<cfif ISDEFINED("form.city") AND form.city NEQ "">
where city = '#trim(city)#'
</cfif>
</cfif>

You will have to expand this out to include the rest of the parameters and it may be better to build the whole query string outside the query.


DonOmite
<a href="

http://www.donomite.com">Destroying

Your Fear of The Web</a>

 

[msCrb]

Thanks DonHawaii, that worked great. Below is how I expanded on the other parameters, I found myself trying to cover every possible way that the user may try to search on. I tested the screens and it seems to work, I'm not sure if I could have coded it an easier way though. What I have discovered is that I got confused with all of the if statements and am not sure where I need to handle if no records are returned since I used the last <cfelse> to handle if they hit submit without entering a search criteria.

<CFQUERY DATASOURCE="#MESSADSN#" NAME="Pharmacy">
SELECT DISTINCT name, address1, address2, city, state, zip, county, phone
FROM pharmacy

<cfif ISDEFINED("form.county") AND form.county NEQ "">
where county = '#trim(county)#'
<cfif ISDEFINED("form.city") AND form.city NEQ "">
AND city = '#trim(city)#'
</cfif>
<cfif ISDEFINED("form.zip") AND form.zip NEQ "">
AND zip = '#trim(zip)#'
</cfif>
<cfif isDefined("form.name")AND form.name NEQ "">
AND name like '#trim(name)#%'
</cfif>
<cfelseif ISDEFINED("form.city") AND form.city NEQ "">
where city = '#trim(city)#'
<cfif ISDEFINED("form.zip") AND form.zip NEQ "">
AND zip = '#trim(zip)#'
</cfif>
<cfif isDefined("form.name")AND form.name NEQ ""> AND name like '#trim(name)#%'
</cfif>
<cfelseif ISDEFINED("form.zip") AND form.zip NEQ "">
where zip = '#trim(zip)#'
<cfif isDefined("form.name")AND form.name NEQ ""> AND name like '#trim(name)#%'
</cfif>
<cfelseif ISDEFINED("form.name") AND form.name NEQ "">
where name like '#trim(name)#%'
<cfelse>
where name like '#trim(name)#%'
</cfif>

ORDER BY name
</CFQUERY>

Cathy

 

[DonHawaii]

Aloha,

One way of simplifying this is to first check that ANY of the form fields are filled in.

If not, then you don't go any further.

If even 1 is filled in you start the string with:
[in cf not in the query]
<cfset sqlStr = "WHERE 1=1">
Throwing in the 1=1, which will always be true, makes the rest of it easier and has no impact really on the rest of the where statement.
Then you just loop over the fields in the form and just add in
<cfset sqlStr = sqlStr + "AND ....">

Then in the query you put in

Select ...
From ...
#sqlStr#

DonOmite

http://www.donomite.com

 

[msCrb]

This is my first time trying to code something like this, did I do this correctly? I got the error below. Also, where should I be using a IF statement to find out if one of the fields were filled out or not so that it will know to bypass the queries?

The value "WHERE 1=1" cannot be converted to a number

The error occurred in E:\MESSA

http://WWW\MESSA\Public\ProviderDirectories\results3.cfm:

line 8

6 : #sqlStr#
7 : <cfif ISDEFINED("form.county") AND form.county NEQ "">
8 : <cfset sqlStr = sqlStr + "AND county = '#trim(county)#'">
9 : <cfif ISDEFINED("form.city") AND form.city NEQ "">
10 : <cfset sqlStr = sqlStr + "AND city = '#trim(city)#'">


----new code----------
<cfset sqlStr = 'WHERE 1=1'>
<CFQUERY DATASOURCE="#MESSADSN#" NAME="Pharmacy">
SELECT DISTINCT name, address1, address2, city, state, zip, county, phone
FROM pharmacy
#sqlStr#
<cfif ISDEFINED("form.county") AND form.county NEQ "">
<cfset sqlStr = sqlStr + "AND county = '#trim(county)#'">
<cfif ISDEFINED("form.city") AND form.city NEQ "">
<cfset sqlStr = sqlStr + "AND city = '#trim(city)#'">
</cfif>
<cfif ISDEFINED("form.zip") AND form.zip NEQ "">
<cfset sqlStr = sqlStr + "AND zip = '#trim(zip)#'">
</cfif>
<cfif isDefined("form.name")AND form.name NEQ "">
<cfset sqlStr = sqlStr + "AND name like '#trim(name)#%'">
</cfif>
<cfelseif ISDEFINED("form.city") AND form.city NEQ "">
<cfset sqlStr = sqlStr + "AND city = '#trim(city)#'">
<cfif ISDEFINED("form.zip") AND form.zip NEQ "">
<cfset sqlStr = sqlStr + "AND zip = '#trim(zip)#'">
</cfif>
<cfif isDefined("form.name")AND form.name NEQ "">
<cfset sqlStr = sqlStr + "AND name like '#trim(name)#%'">
</cfif>
<cfelseif ISDEFINED("form.zip") AND form.zip NEQ "">

<cfset sqlStr = sqlStr + "AND zip = '#trim(zip)#'">
<cfif isDefined("form.name")AND form.name NEQ "">
<cfset sqlStr = sqlStr + "AND name like '#trim(name)#%'">
</cfif>
<cfelseif ISDEFINED("form.name") AND form.name NEQ "">
<cfset sqlStr = sqlStr + "AND name like '#trim(name)#%'">
<cfelse>
<cfset sqlStr = sqlStr + "AND name like '#trim(name)#%'">

</cfif>

ORDER BY name
</CFQUERY>

Cathy

 

[imstillatwork]

dont' make it too difficult..


SELECT DISTINCT name, address1, address2, city, state, zip, county, phone
FROM pharmacy
WHERE 0 = 0
<cfif structKeyExists(form.city) AND len(form.city)>
AND city = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.city#">
</cfif>

<cfif structKeyExists(form.county) AND len(form.county)>
AND county = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.county#">
</cfif>
[more like above if needed]

ORDER BY name


WHERE 0 = 0 has worked for me in every DB i've ever used.

Really, though, delete everything you have there, and try it as simple as possible. don't hard code in every possible combination.. take each form field as it's own. only include in the WHERE clause those form fields that have something in them.



Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[r937]

you can use either WHERE 1=1 or WHERE 0=0, that's not the problem

the problem is here --

<cfset sqlStr = sqlStr + "AND ...

coldfusion requires & for string concatenation and + for addition of numbers

r937.com | rudy.ca

 

[imstillatwork]

I guess that's what I was saying, I just have the habit of 0=0

My main point was to just use a cfif for each field that you wanted to search by,a dn keep the code simple.


Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[msCrb]

Thanks everyone for all of the help, I tried a couple of different scenerios and here are the results. I replaced the <cfset sqlStr = sqlStr + "AND ... to <cfset sqlStr = sqlStr & "AND ... and that resolved the issue with the error I was receiving (The value "WHERE 1=1" cannot be converted to a number) after I replaced it I didn't get an error but it started returning all of the records in the database no matter what search criteria I selected. It didn't even get down to the if statements, probably an error in the logic somewhere. I then tried the suggestion for the structKeyExists, read futher down for the code I used for that.

<cfset sqlStr = 'WHERE 1=1'>
<CFQUERY DATASOURCE="#MESSADSN#" NAME="Pharmacy">
SELECT DISTINCT name, address1, address2, city, state, zip, county, phone
FROM pharmacy
#sqlStr#
<cfif ISDEFINED("form.county") AND form.county NEQ "">
<cfset sqlStr = sqlStr & "AND county = '#trim(county)#'">
<cfif ISDEFINED("form.city") AND form.city NEQ "">
<cfset sqlStr = sqlStr & "AND city = '#trim(city)#'">
</cfif>
<cfif ISDEFINED("form.zip") AND form.zip NEQ "">
<cfset sqlStr = sqlStr & "AND zip = '#trim(zip)#'">
</cfif>
<cfif isDefined("form.name")AND form.name NEQ "">
<cfset sqlStr = sqlStr & "AND name like '#trim(name)#%'">
</cfif>
<cfelseif ISDEFINED("form.city") AND form.city NEQ "">
<cfset sqlStr = sqlStr & "AND city = '#trim(city)#'">
<cfif ISDEFINED("form.zip") AND form.zip NEQ "">
<cfset sqlStr = sqlStr & "AND zip = '#trim(zip)#'">
</cfif>
<cfif isDefined("form.name")AND form.name NEQ "">
<cfset sqlStr = sqlStr & "AND name like '#trim(name)#%'">
</cfif>
<cfelseif ISDEFINED("form.zip") AND form.zip NEQ "">
<cfset sqlStr = sqlStr & "AND zip = '#trim(zip)#'">
<cfif isDefined("form.name")AND form.name NEQ "">
<cfset sqlStr = sqlStr & "AND name like '#trim(name)
#%'">
</cfif>
<cfelseif ISDEFINED("form.name") AND form.name NEQ "">
<cfset sqlStr = sqlStr & "AND name like '#trim(name)#%'">
<cfelse>
<cfset sqlStr = sqlStr & "AND name like '#trim(name)#%'">
</cfif>
ORDER BY name
</CFQUERY>


using structkey, I have never heard of this but I gave it a try, It says that it takes two parameters the structure to check and the key to look for. I have no idea what my structure is so I put in form.fieldname, fieldname, anyway I received the error below. I also to the suggestion of deleting everything and keeping it simple and not trying to code for every possible combination. I never thought about that before, I don't know what I was thinking. The code that I finally ended up using that worked is very clean. (see final code at the bottom) I didn't realize that it would work I was looking for it to be complicated so I made it complicated. Even though I got my code to work I really would like to know how the structkeyExist was suppose to work.

You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.


The error occurred in E:\MESSA

http://WWW\MESSA\Public\ProviderDirectories\results4.cfm:

line 8

6 : WHERE 0=0
7 :
8 : <cfif structKeyExists(form.county,"county") AND len(form.county)>
9 : AND county = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.county#">
10 : </cfif>


<CFQUERY DATASOURCE="#MESSADSN#" NAME="Pharmacy">
SELECT DISTINCT name, address1, address2, city, state, zip, county, phone
FROM pharmacy
WHERE 0=0

<cfif structKeyExists(form.county,"county") AND len
(form.county)>
AND county = <cfqueryparam cfsqltype="CF_SQL_VARCHAR"
value="#form.county#">
</cfif>

<cfif structKeyExists(form.city,"city") AND len(form.city)>
AND city = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.city#">
</cfif>

<cfif structKeyExists(form.zip,"zip") AND len(form.zip)>
AND zip = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.zip#">
</cfif>

<cfif structKeyExists(form.name,"name") AND len(form.name)>
AND name = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.name#">
</cfif>

ORDER BY name
</CFQUERY>

This is the code I ended up using.

<CFQUERY DATASOURCE="#MESSADSN#" NAME="Pharmacy">
SELECT DISTINCT name, address1, address2, city, state, zip, county, phone
FROM pharmacy
WHERE 0=0
<cfif ISDEFINED("form.county") AND form.county NEQ "">
AND county = '#trim(county)#'
</cfif>
<cfif ISDEFINED("form.city") AND form.city NEQ "">
AND city = '#trim(city)#'
</cfif>
<cfif ISDEFINED("form.zip") AND form.zip NEQ "">
AND zip = '#trim(zip)#'
</cfif>
<cfif isDefined("form.name")AND form.name NEQ "">
AND name like '#trim(name)#%'
</cfif>

ORDER BY name
</CFQUERY>

Cathy

Cathy

 

[r937]

i would do it like this --

<CFPARAM NAME="form.county" DEFAULT="" >
<CFPARAM NAME="form.city" DEFAULT="" >
<CFPARAM NAME="form.zip" DEFAULT="" >
<CFPARAM NAME="form.name" DEFAULT="" >
<CFQUERY DATASOURCE="#MESSADSN#" NAME="Pharmacy">
SELECT DISTINCT name, address1, address2, city, state, zip, county, phone
FROM pharmacy
WHERE 1=1
<cfif Len(Trim(form.county)) >
AND county = '#Trim(form.county)#'
</cfif>
<cfif Len(Trim(form.city)) >
AND city = '#Trim(form.city)#'
</cfif>
<cfif Len(Trim(form.zip)) >
AND zip = '#Trim(form.zip)#'
</cfif>
<cfif Len(Trim(form.name)) >
AND name like '#Trim(form.name)#%'
</cfif>
ORDER BY name
</CFQUERY>

r937.com | rudy.ca

 

[msCrb]

Why would you use that instead of isDefined? I'm asking because I never know what's best to use with which scenerios. Thanks

Cathy

 

[r937]

1. i heard isDefined is slow

2. i like the brevity of <CFIF Len(form.foo) >



r937.com | rudy.ca

 

[DonHawaii]

Wow. This became really complicated. Forget all that structure key stuff and just go simple. It will work. Honest.

DonOmite

http://www.donomite.com

 

[imstillatwork]

structKeyExists complicated? that is the proper way to check if a key of a structure exists.... the entire FORM scope is a structure.

My reasons for NOT concatenating a string into a part of a query is that you CAN NOT use cfqueryparam. Not using cfqueryparam in a query accepting data from the ouside world is not that wise.

I agree with rudy about using len() also. or, you can use the JAVA form.whatever.length() method if that makes you more comfy.

So just for justification, my way was no more complicated then what he was ended up being done. I just added cfqueryparam, and I prefer to use structKeyExists. isDefined will screw you over in many cases when working with more complicated structures.

Rudys use of param for the form vars is plenty acceptable too, but one of the areas we probably differ in our chosen use of the language. Actually. cfparaming things at the top of the script can help readability a lot.


Kevin

http://www.lot-o-nothin.com/cfml/

Phase 1: Read the CFML Reference
Phase 2: ???
Phase 3: Profit!

 

[msCrb]

Kevin, what does the error below mean that I received. In my case what would the two parameters had been?

Cathy

You have attempted to dereference a scalar variable of type class java.lang.String as a structure with members.


The error occurred in E:\MESSA

http://WWW\MESSA\Public\ProviderDirectories\results4.cfm:

line 8

6 : WHERE 0=0
7 :
8 : <cfif structKeyExists(form.county,"county") AND len(form.county)>
9 : AND county = <cfqueryparam cfsqltype="CF_SQL_VARCHAR" value="#form.county#">
10 : </cfif>



Cathy

 

[r937]

change this --

<cfif structKeyExists(form.county,"county")

to this --

<cfif structKeyExists(form,"county")

r937.com | rudy.ca

 

[msCrb]

I made the code change and it worked.

Thanks,
Cathy

Cathy