• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 172
  • Last Modified:

SQL BETWEEN question

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
Bang-O-Matic
Asked:
Bang-O-Matic
  • 6
  • 3
  • 2
  • +1
1 Solution
 
erikTsomikSystem Architect, CF programmer Commented:
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
 
Daniel WilsonCommented:
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
 
Bang-O-MaticAuthor Commented:
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
Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

 
erikTsomikSystem Architect, CF programmer Commented:
JUst get rid og the BEtween operator and the quotes
0
 
Bang-O-MaticAuthor Commented:
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
 
azadisaryevCommented:
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
 
Bang-O-MaticAuthor Commented:
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
 
azadisaryevCommented:
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
 
Bang-O-MaticAuthor Commented:
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
 
azadisaryevCommented:
<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
 
Bang-O-MaticAuthor Commented:
that worked! thanks!
0
 
Bang-O-MaticAuthor Commented:
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

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 6
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now