Solved

SQL BETWEEN question

Posted on 2008-10-01
12
144 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
 
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
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.

 

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

Scale it in WD Gold

With up to ten times the workload capacity of desktop drives, WD Gold hard drives employ advanced technology to deliver among the best in reliability, capacity, power efficiency and performance.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Learn how to create flexible layouts using relative units in CSS.  New relative units added in CSS3 include vw(viewports width), vh(viewports height), vmin(minimum of viewports height and width), and vmax (maximum of viewports height and width).
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to us…

896 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

13 Experts available now in Live!

Get 1:1 Help Now