Solved

SQL BETWEEN question

Posted on 2008-10-01
12
149 Views
Last Modified: 2013-12-24
In my property search form, I'm trying to avoid having 2 fields (min) and (max) Im using a drop down list to search betwteen 2 acreage values.
------------------------------------------
<select name="Acres" id="Acres">
          <option value=" " selected>Any
          <option value="0 AND 1">1 acre or less
          <option value="1 AND 5">1 to 5 acres
          <option value="5 AND 20">5 to 20 acres
          <option value="20 AND 50">20 to 50 acres
          <option value="50 AND 75">50 to 75 acres
          <option value="75 AND 100">75 to 100 acres
          <option value=">= 100">100 acres and up
        </select>
------------------------
error below
The problem is that BETWEEN "1 and 5" should be '1' AND '5'
-----------------------
SELECT mlsID,City,Zoning,SubType,Price,StreetNumber,StreetName,Acres FROM listingsland WHERE 0=0 AND Price BETWEEN '1' AND '50000000' AND acres BETWEEN "1 AND 5" ORDER BY Price ASC

-----------------------------

How can I fix 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
  • 6
  • 3
  • 2
  • +1
12 Comments
 
LVL 19

Expert Comment

by:erikTsomik
ID: 22614677
Can you just say

<select name="Acres" id="Acres">
          <option value=" " selected>Any
          <option value=" IN (0,1)">1 acre or less
          <option value="IN (1,5)">1 to 5 acres
          <option value="IN (5,20)">5 to 20 acres
          <option value="IN (20,50)">20 to 50 acres
          <option value="50 AND 75">50 to 75 acres
          <option value="75 AND 100">75 to 100 acres
          <option value=">= 100">100 acres and up
        </select>

And then change the query to
SELECT mlsID,City,Zoning,SubType,Price,StreetNumber,StreetName,Acres FROM listingsland WHERE 0=0 AND Price BETWEEN '1' AND '50000000' AND acres #form.acres#ORDER BY Price ASC
0
 
LVL 32

Expert Comment

by:Daniel Wilson
ID: 22614825
won't
IN (1,5)
omit a property of, say, 4.5 acres?

What if you put the BETWEEN in your value?
<option value="BETWEEN 1 AND 5">1 to 5 acres

Does CF handle that appropriately?

0
 

Author Comment

by:Bang-O-Matic
ID: 22614835
Erik that didnt work.


Error Executing Database Query.  
[Macromedia][SequeLink JDBC Driver][ODBC Socket][MySQL][ODBC 3.51 Driver][mysqld-4.1.21-community-nt]You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'ORDER BY Price ASC' at line 1  
 
The error occurred in C:\Inetpub\idx\LCBOR\land\results.cfm: line 52
 
50 : <cfif form.acres IS NOT " ">
51 :   AND acres BETWEEN "#form.acres#"
52 : </cfif>
53 : ORDER BY Price ASC
54 : </cfquery>

 

--------------------------------------------------------------------------------
 
SQL    SELECT mlsID,City,Zoning,SubType,Price,StreetNumber,StreetName,Acres FROM listingsland WHERE 0=0 AND Price BETWEEN '1' AND '50000000' AND acres BETWEEN "IN (1,5)" ORDER BY Price ASC  
DATASOURCE   lcbor
VENDORERRORCODE   1064
SQLSTATE   42000
 
0
Flexible connectivity for any environment

The KE6900 series can extend and deploy computers with high definition displays across multiple stations in a variety of applications that suit any environment. Expand computer use to stations across multiple rooms with dynamic access.

 
LVL 19

Expert Comment

by:erikTsomik
ID: 22614861
JUst get rid og the BEtween operator and the quotes
0
 

Author Comment

by:Bang-O-Matic
ID: 22614873
Daniel I tried that also and it didnt work:

SELECT mlsID,City,Zoning,SubType,Price,StreetNumber,StreetName,Acres FROM listingsland WHERE 0=0 AND Price BETWEEN '1' AND '50000000' AND acres "BETWEEN 1 AND 5" ORDER BY Price ASC
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 22615007
is ACRES a text field in your db? if so, why? and why is your PRICE field a text field, too?

because you do not need ' around numeric values in sql statement, only text values.

you query should work fine if ACRES field is of numeric type and you have something like:
<cfquery ...>
....
... AND acres BETWEEN #form.acres# ...
</cfquery>
0
 

Author Comment

by:Bang-O-Matic
ID: 22615423
azadisaryev,
no, Acres is an integer and so is Price.  the problem is that with the code in my form dropdown list the database is being query as acres BETWEEN "1 AND 5".  the correct syntax is: acres BETWEEN '1' AND '5'    I just need to know how to code the dropdown list in order to reflect this.
0
 
LVL 27

Expert Comment

by:azadisaryev
ID: 22615584
the correct syntax is: acres BETWEEN 1 AND 5 - without any ' around the numbers.
in my test using

acres BETWEEN #form.acres#

executes correctly.

can you post your exact cfquery code? are you enclosing your form variables in your SQL in any quotes by any chance? you should not.
0
 

Author Comment

by:Bang-O-Matic
ID: 22615695
search page code
-------------------------
<cfquery name="lomls" datasource="LCBOR">
SELECT DISTINCT City
FROM listingsland
ORDER BY City ASC
</cfquery>
<cfquery name="lomls2" datasource="LCBOR">
SELECT DISTINCT SubType
FROM listingsland
ORDER BY SubType ASC
</cfquery>
<cfquery name="lomls3" datasource="LCBOR">
SELECT DISTINCT Zoning
FROM listingsland
ORDER BY Zoning ASC
</cfquery>

<html>
<head>
<title>Property Search</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
</head>
<body topmargin="35">
<form action="results.cfm" method="post" enctype="multipart/form-data" name="form">
  <table align="center" width="450" border="0" cellspacing="0" cellpadding="0">
    <tr bgcolor="#cccccc">
      <td colspan="3"><div align="center"><font color="#FFFFFF" size="4"><img src="/images/searchland.gif" width="500" height="50"></font></div></td>
    </tr>
    <tr>
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr bgcolor="#f2f7f2">
      <td colspan="3"><font color="#004080"><strong><font color="#004080">city
              <select name="City" id="City">
                <option value=" ">Any</option>
                <cfoutput query = "lomls">
                  <option value="#City#">#City#</option>
                </cfoutput>
              </select>
      </font></strong></font></td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr bgcolor="#FFFFFF">
      <td colspan="3" bgcolor="#f2f7f2"><font color="004080"><strong>property zoning
            <select name="Zoning" id="Zoning">
              <option value=" ">Any</option>
              <cfoutput query = "lomls3">
                <option value="#Zoning#">#Zoning#</option>
              </cfoutput>
            </select>
      </strong></font></td>
    </tr>
    <tr bgcolor="#f2f7f2">
      <td colspan="3" bgcolor="#FFFFFF">&nbsp;</td>
    </tr>
    <tr bgcolor="#f2f7f2">
      <td colspan="3" bgcolor="#f2f7f2"><font color="004080"><strong>subdivision type</strong></font>
        <select name="SubType" id="SubType">
          <option value=" ">Any</option>
          <cfoutput query = "lomls2">
            <option value="#SubType#">#SubType#</option>
          </cfoutput>
        </select>        <font color="#004080">&nbsp;</font></td>
    </tr>
    <tr>
      <td width="184"><font color="#004080">&nbsp;</font></td>
      <td width="30"><font color="#004080">&nbsp;</font></td>
      <td width="286"><font color="#004080">&nbsp;</font></td>
    </tr>


<tr bgcolor="#f2f7f2">
     
      <td colspan="3"><strong><font color="#004080">acreage
        </font></strong>
        <select name="Acres" id="Acres">
          <option value=" " selected>Any
          <option value="0 AND 1">1 acre or less
          <option value="1 AND 5">1 to 5 acres
          <option value="5 AND 20">5 to 20 acres
          <option value="20 AND 50">20 to 50 acres
          <option value="50 AND 75">50 to 75 acres
          <option value="75 AND 100">75 to 100 acres
          <option value="=> 100">100 acres and up
        </select>
      </td>
    </tr>


 <tr bgcolor="#FFFFFF">
      <td colspan="3">&nbsp;</td>
    </tr>



    <tr bgcolor="#f2f7f2">
      <td><strong><font color="#004080">price
        <select name="minimumprice">
          <option value="1" selected>No Minimum</option>
          <option value="10000">$10,000</option>
          <option value="25000">$25,000</option>
          <option value="50000">$50,000</option>
          <option value="75000">$75,000</option>
          <option value="100000">$100,000</option>
          <option value="125000">$125,000</option>
          <option value="150000">$150,000</option>
          <option value="175000">$175,000</option>
          <option value="200000">$200,000</option>
          <option value="225000">$225,000</option>
          <option value="250000">$250,000</option>
          <option value="275000">$275,000</option>
          <option value="300000">$300,000</option>
          <option value="350000">$350,000</option>
          <option value="400000">$400,000</option>
          <option value="450000">$450,000</option>
          <option value="500000">$500,000</option>
          <option value="600000">$600,000</option>
          <option value="700000">$700,000</option>
          <option value="750000">$750,000</option>
          <option value="800000">$800,000</option>
          <option value="900000">$900,000</option>
          <option value="1000000">$1M</option>
          <option value="3000000">$3M</option>
          <option value="5000000">$5M</option>
          <option value="7000000">$7M</option>
          <option value="10000000">$10M</option>
        </select>
        </font></strong></td>
      <td><font color="#004080">&nbsp;</font></td>
      <td><strong><font color="#004080">to
        <select name="maximumprice">
          <option value="50000000" selected>No Maximum</option>
          <option value="10000">$10,000</option>
          <option value="25000">$25,000</option>
          <option value="50000">$50,000</option>
          <option value="75000">$75,000</option>
          <option value="100000">$100,000</option>
          <option value="125000">$125,000</option>
          <option value="150000">$150,000</option>
          <option value="175000">$175,000</option>
          <option value="200000">$200,000</option>
          <option value="225000">$225,000</option>
          <option value="250000">$250,000</option>
          <option value="275000">$275,000</option>
          <option value="300000">$300,000</option>
          <option value="350000">$350,000</option>
          <option value="400000">$400,000</option>
          <option value="450000">$450,000</option>
          <option value="500000">$500,000</option>
          <option value="600000">$600,000</option>
          <option value="700000">$700,000</option>
          <option value="750000">$750,000</option>
          <option value="800000">$800,000</option>
          <option value="900000">$900,000</option>
          <option value="1000000">$1M</option>
          <option value="3000000">$3M</option>
          <option value="5000000">$5M</option>
          <option value="7000000">$7M</option>
          <option value="10000000">$10M</option>
        </select>
        </font></strong></td>
    </tr>
    <tr>
      <td><font color="#004080">&nbsp;</font></td>
      <td><font color="#004080">&nbsp;</font></td>
      <td><font color="#004080">&nbsp;</font></td>
    </tr>
   
    <tr>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td bgcolor="#FFFFFF"><font color="#666666"><strong>OR</strong></font></td>
      <td bgcolor="#FFFFFF">&nbsp;</td>
      <td>&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3" bgcolor="#FFFFFF">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3" bgcolor="#f2f7f2"><div align="left"><strong><font color="#004080">mls #
        <input name="MlsID" type="text" id="MlsID" size="14">
      </font></strong></div></td>
    </tr>
    <tr>
      <td colspan="3">&nbsp;</td>
    </tr>
    <tr>
      <td colspan="3">
       
        <div align="left">
          <input type="submit" name="Submit" value="Search Land &amp; Lots">
          </div></td>
    </tr>
  </table>
</form>
<p align="center">&nbsp;</p>
</body>
</html>




Query on my results page
----------------------------------------------------------
<cfquery name = "lomlsland" datasource="lcbor">
SELECT mlsID,City,Zoning,SubType,Price,StreetNumber,StreetName,Acres FROM listingsland
WHERE 0=0
<!---Search by Zoning--->
<cfif form.Zoning IS NOT " ">
  AND Zoning LIKE '%#form.Zoning#%'
</cfif>
<!---Search by Subdivision Type--->
<cfif form.SubType IS NOT " ">
  AND SubType LIKE '%#form.SubType#%'
</cfif>
<!---Search by City--->
<cfif form.City IS NOT " ">
  AND City LIKE '%#form.City#%'
</cfif>
<cfif isdefined("form.minimumprice") and isdefined("form.maximumprice")>
  AND Price BETWEEN '#form.minimumprice#' AND '#form.maximumprice#'
</cfif>
<!---Search by Acres--->
<cfif form.acres IS NOT " ">
  AND acres BETWEEN "#form.acres#"
</cfif>
ORDER BY Price ASC
</cfquery>
0
 
LVL 27

Accepted Solution

by:
azadisaryev earned 500 total points
ID: 22615805
<cfif form.acres IS NOT " ">
  AND acres BETWEEN "#form.acres#"
</cfif>

remove those " from around #form.acres.

you also don;t need ' around your #form.minimumprice# and #form.maximumprice#

you are also aware that if a use select the last option from your form's ACRES list your query will fail with a syntax error, right?
0
 

Author Closing Comment

by:Bang-O-Matic
ID: 31501982
that worked! thanks!
0
 

Author Comment

by:Bang-O-Matic
ID: 22616031
It also may be worth mentioning that you also don;t need ' around your #form.minimumprice# and #form.maximumprice#, remove those and it caused an error. put them back and it works fine.
0

Featured Post

Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering 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

Suggested Solutions

Title # Comments Views Activity
Mysql Left Join Case 10 115
MySQL 5.6.30 - daily outages 46 86
MySQL-Design Help 12 67
Watching Inbound/Outbound Traffic on Server 4 26
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

751 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