Solved

SQL OR operator question

Posted on 2014-03-14
5
200 Views
Last Modified: 2014-03-14
On my real estate search page form I have a distinct query that pulls in all the property types, "type", and it's quite a few in my dropdown select!  I'd like to group some of the property types together to make the select dropdown more user friendly. Such as I'd like to group the type "condo" with "townhouse" and "zero lot line" in my form select dropdown, instead of having :

Condo
Townhouse
Zero Lot Line

I want:

Condo / Townhouse / Zero Lot Line




My form submits to results page query:

<cfif StructKeyExists(form,"Type") and len(trim(form.Type))>
 AND Type = (
 <cfqueryparam value="#form.Type#" cfsqltype="cf_sql_varchar" list="true">
     )
</cfif>


So on my search form select I tried to hard code the property type like this:

 <option value="Townhouse" OR Type="Condominium" OR Type="Zero Lot Line">Condo / Townhouse / Zero Lot Line</option>



It does not throw an error, but it only queries the first type "Townhouse" and not condominium or zero lot line.

Can someone show me a better way to code this?

Thanks!
0
Comment
Question by:Bang-O-Matic
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
5 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
ID: 39929721
Make the option value a list. Use a character unlikely to appear in the data, maybe a "|" symbol:

<option value="Townhouse|Condominium|Zero Lot Line">....

Then change your query to that delimiter with an IN clause:

<cfif StructKeyExists(form,"Type") and listLen(trim(form.Type), "|")>
 AND Type IN
        (
             <cfqueryparam value="#form.Type#"
                   cfsqltype="cf_sql_varchar"
                   list="true"
                   separator="|">
     )
</cfif>
0
 

Author Comment

by:Bang-O-Matic
ID: 39929740
Hi _agx_ , Works perfect! Thank you so much!, again, LOL
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39929744
... OR if you have a separate PropertyType table, you could add a "category" column.

Category
1  |  Category A
2  |  Category B

PropertyType    |  CategoryID
Condo              |  1  <=== Category  A
Townhouse       |  1  <=== Category  A
Zero Lot Line   |  1  <=== Category  A
Duplex             |  2  <=== Category  B
Ranch              |  ......

Then filter on the categoryID value instead. Not tested, but something like:

<select name="categoryID" ...>
  <option value="#CategoryID#">Condo / Townhouse / Zero Lot Line</option>
 .....

<cfif StructKeyExists(form,"Type") and listLen(trim(form.Type), "|")>
 AND CategoryID =  <cfqueryparam value="#form.CategoryID#"
                   cfsqltype="cf_sql_integer" >
</cfif>
0
 

Author Closing Comment

by:Bang-O-Matic
ID: 39929746
Quick, concise and perfect! Thank you _agx_
0
 
LVL 52

Expert Comment

by:_agx_
ID: 39929750
Welcome :) Any time
0

Featured Post

Learn by Doing. Anytime. Anywhere.

Do you like to learn by doing?
Our labs and exercises give you the chance to do just that: Learn by performing actions on real environments.

Hands-on, scenario-based labs give you experience on real environments provided by us so you don't have to worry about breaking anything.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today, I was working on some optimization and spam-stopping techniques when I encountered Ben Nadel's post to reduce spam feature using Math (http://www.bennadel.com/blog/197-How-I-Stop-Spammers-On-My-ColdFusion-Blog.htm). While this method is not o…
CFGRID Custom Functionality Series -  Part 1 Hi Guys, I was once asked how it is possible to to add a hyperlink in the cfgrid and open the window to show the data. Now this is quite simple, I have to use the EXT JS library for this and I achiev…
If you’ve ever visited a web page and noticed a cool font that you really liked the look of, but couldn’t figure out which font it was so that you could use it for your own work, then this video is for you! In this Micro Tutorial, you'll learn yo…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

634 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