?
Solved

SQL OR operator question

Posted on 2014-03-14
5
Medium Priority
?
204 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 53

Accepted Solution

by:
_agx_ earned 2000 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 53

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 53

Expert Comment

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

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
With just a little bit of  SQL and VBA, many doors open to cool things like synchronize a list box to display data relevant to other information on a form.  If you have never written code or looked at an SQL statement before, no problem! ...  give i…
This video tutorial shows you the steps to go through to set up what I believe to be the best email app on the android platform to read Exchange mail.  Get the app on your phone: The first step is to make sure you have the Samsung Email app on your …

598 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