Solved

SQL OR operator question

Posted on 2014-03-14
5
192 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
  • 3
  • 2
5 Comments
 
LVL 52

Accepted Solution

by:
_agx_ earned 500 total points
Comment Utility
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
Comment Utility
Hi _agx_ , Works perfect! Thank you so much!, again, LOL
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
... 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
Comment Utility
Quick, concise and perfect! Thank you _agx_
0
 
LVL 52

Expert Comment

by:_agx_
Comment Utility
Welcome :) Any time
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Suggested Solutions

PL/SQL can be a very powerful tool for working directly with database tables. Being able to loop will allow you to perform more complex operations, but can be a little tricky to write correctly. This article will provide examples of basic loops alon…
Recently while working on a project I got a very annoying cfdocument has no body error message. I had never seen this error before. So I checked the code. The code was pretty simple; it was Just showing me the cfdocumnt tag and inside that tag a …
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

743 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

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now