Solved

SQL BETWEEN question

Posted on 2008-10-01
12
145 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
  • 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

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

Suggested Solutions

If you don't have the right permissions set for your WordPress location in IIS, you won't be able to perform automatic updates. Here's how to fix the problem.
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…
Windows 10 is mostly good. However the one thing that annoys me is how many clicks you have to do to dial a VPN connection. You have to go to settings from the start menu, (2 clicks), Network and Internet (1 click), Click VPN (another click) then fi…
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

806 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