Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

SQL BETWEEN question

Posted on 2008-10-01
12
Medium Priority
?
169 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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
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 2000 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 learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

This post contains step-by-step instructions for setting up alerting in Percona Monitoring and Management (PMM) using Grafana.
In this blog post, we’ll look at how using thread_statistics can cause high memory usage.
In this video, Percona Solution Engineer Dimitri Vanoverbeke discusses why you want to use at least three nodes in a database cluster. To discuss how Percona Consulting can help with your design and architecture needs for your database and infras…
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
Suggested Courses
Course of the Month13 days, 10 hours left to enroll

963 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