Search with SQL WHERE. AND or OR?? or what? 3

[DrDan2]

Hi. Small problem. I have a search page. On it I have search by Country and by City. If both fields are filled in there's no problem. If just one is filled in it still looks for two. e.g. I search for the country "Kenya" but don't enter anything on the form for city, then even if I have 50 entries in my Database for Kenya, it will only return the records that have "Kenya" for country and a null value for a city.

How do I get it to bring up All records where Country="Kenya" even if City=Null?

This SQL code doesn't do the job:

<CFQUERY Name=&quot;DiveSearch&quot; DATASOURCE=&quot;DiveSchools&quot;>
Select *
From DiveData
Where Country='#Country#' AND City='#City#'
</CFQUERY>

Thanks.

 

[r937]

one way of doing it is to allow either or both of the form fields to be blank (if they're both blank, then presumably the user wants to see every city in every country)

i suppose you could set up some elaborate series of if/else tests, such that one or the other must be filled in, and then construct the WHERE clause accordingly, i.e. if country is left off then you want WHERE City='foo' and if city is left off then you want WHERE Country='Bar'...

... but that can be really cumbersome to code

i prefer the following, which has no additional validity checks, and allows either one or the other or both form fields to be left blank, with exactly the behaviour you would expect

<CFQUERY Name=&quot;DiveSearch&quot; DATASOURCE=&quot;DiveSchools&quot;>
select diveschoolid
, diveschoolname
, diveschoolrating
from DiveData
where 1=1
<CFIF Len(form.Country)>
and Country='#form.Country#'
</CFIF>
<CFIF Len(form.City)>
and City='#form.City#'
</CFIF>
</CFQUERY>

notice how each of the AND clauses can either be present or not, either both or neither, and the query will still run the way you expect

rudy

 

[DrDan2]

Thanks. Too tired to try it out now tho. Where you typed, &quot;Where 1=1&quot; Whats the 1=1 for?

I am new to ColdFusion.

Thanks again.

 

[neofactor]

Sure... Try this:

<CFQUERY Name=&quot;DiveSearch&quot; DATASOURCE=&quot;DiveSchools&quot;>
  Select *
  From DiveData
  
  Where 1=1

	<CFIF TRIM(URL.Country) neq &quot;&quot; AND TRIM(URL.City) eq &quot;&quot;>
	  AND  Country	=	'#TRIM(URL.Country)#'
	  
	<CFELSEIF TRIM(URL.Country) eq &quot;&quot; AND TRIM(URL.City) neq &quot;&quot;>
	  AND  City	=	'#TRIM(URL.City)#'
	  
	<CFELSEIF TRIM(URL.Country) neq &quot;&quot; AND TRIM(URL.City) neq &quot;&quot;>
	  AND  Country	=	'#TRIM(URL.Country)#'
	  AND  City	=	'#TRIM(URL.City)#'

	</CFIF>

</CFQUERY>

You start the Were clause off with a 10=1 That way the next line can start with and AND.... Good practice in case all the CFIF's are false.. meaning nothing was in either field.. you would not get a SQL error.

I didn't know if you used a Get or Post so I assumed URL for get... otherwise replace URL with FORM for a post.

The WHERE clause is based on 1 of 4 events:

1) Country (after a trim) contains something... but the City is empty.... Then we just check the COUNTY statement.

2) CITY (after a trim) contains something... but the COUNRTY is empty.... Then we just check the CITY statement.

3) Both CITY and COUNTY have value and we check for Both

4) If both are empty then 1=1 will result showing all records.... but I would run a check for this 1st before the query and say.. you need to enter info in at least one feild before I can check... that way you dont get all records back.


Did this help? You might also want to look at wild Card search to check for close matches... try replacing the = with LIKE.. example:

NAME LIKE '%#TRIM(URL.Country)#%'

Let me know if you need more help or if this confused you... Happy Thanksgiving.
David McIntosh

http://www.neofactor.com

Have a nice day!

 

[neofactor]

opps...

I said 10=1 somewhere and it should be 1=1.
I posted and then say two posts already...
man you are fast!


David McIntosh

http://www.neofactor.com

Have a nice day!

 

[r937]

the 1=1, as david explained, is so that subsequent lines can start with AND

but david, yours is needlessly complex, and why wouldn't you allow the user to hit the Search button without making a choice? a lot of the searches i do, i want the ability to see &quot;all&quot;...

the TRIM is not a bad idea, it guards against some bozo typing a couple of blanks into the field just to be persnickety...

rudy

 

[DrDan1]

Thanks guys. Where would I be without Tek-Tips. Probably in the same place but with a head shaped dent in my Keyboard.

In my limited experience I haven't yet come across TRIM. What exactly does that do? Sorry. IT's just that I'm too strapped for cash at the moment to buy a book.
Cheers guys.

 

[neofactor]

Trim() basically strips off any blank spaces off a variable. So if someone hit {space}hello it would drop the {space} Very helpful when you want to see if someone blew by a form with just spaces.. but more so to clean up spaces people accidentally put in.

LEN() just looks if the variable has content.. which would include spaces.

As far as books.... In the beginning I bought Ben's book and still use it as a reference.. but honestly.. the best resource is the free ones like here and countless other places. The Documentation for CF that comes with it is very good too. I would spend some time and look at some of the functions...

My ultimate resource is Ben Forta's book for ColdFusion Certification Study Guide. Small thin book.. cant be more than 90 pages or so.. but it awesome to show you all the relative tags and their parameters.


Hope I helped you....

And To answer the previous post...
Your right it is a bit over complicated...
There are always 10 ways to do everything.

As far as not allowing a defualt showing all variables.. well... I usually inclide that as well in all my searches... I didnt know how many items he had to return, and if he knew about &quot;paging&quot; the results... so all returned results could be a problem.

Enjoy and Happy Holidays!!!!!
David McIntosh

http://www.neofactor.com

Have a nice day!

 

[r937]

you don't need to buy a book, all the CFML docs are online

here's Trim --

http://livedocs.macromedia.com/cf50docs/CFML_Reference/Functions240.jsp#1113998

use the &quot;up&quot; arrow in the top right corner of that page to see what else they gots

 

[DrDan1]

Once again I find myself thanking you for valuable informations. Cheers.