Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2010
    Posts
    8

    Question Unanswered: Querying Numbers from a Database

    I searched the forums to no avail so if there is a previous post that covers this please let me know.

    ---

    I am querying numbers from a database and am having a little trouble. At the moment I am able to query numbers (1, 2, 3) but when it comes to the length of numbers that's where it's not coming through (1 vs. 10 vs. 100). So when a user requests the lowest price accepted for a house (100,000) and maximum price (1,000,000) this returns everything you can think of (example: 10,000,000 and 10).

    Here is a portion of my query below:

    Code:
    mls_listings.price >= '{$_GET['minprice']}' AND 
    mls_listings.price <= '{$_GET['maxprice']}'
    mls_listings.price is the table containing the price

    minprice and max price is exactly as sounds coming from a drop down menu that the user has selected before arriving to this page.

    ---

    Thank you for your help

  2. #2
    Join Date
    Dec 2007
    Location
    Richmond, VA
    Posts
    1,328
    Provided Answers: 5
    What is the complete SQL statement that is being generated? It is probably in the way that it is written that the rest of the listings are getting picked up.
    Dave

  3. #3
    Join Date
    Mar 2010
    Posts
    8
    Code:
    $sql = "SELECT DISTINCT 
        mls_listings.uniqueid,  
        mls_listings.streetaddress, 
        mls_listings.city, 
        mls_listings.price, 
        mls_listings.bedrooms, 
        mls_listings.baths, 
        mls_listings.zipcode, 
        mls_listings.state, 
        mls_listings.description,
        mls_listings.media as image
    FROM 
        mls_listings 
    WHERE 
        (((mls_listings.city IN (
            '{$_GET['redondobeach']}',
            '{$_GET['santamonica']}',
            '{$_GET['specificcity']}',
            '{$_GET['hermosabeach']}',
            '{$_GET['elsegundo']}',
            '{$_GET['manhattanbeach']}',
            '{$_GET['marinadelrey']}',
            '{$_GET['palosverdes']}',
            '{$_GET['playadelrey']}',
            '{$_GET['playavista']}',
            '{$_GET['torrance']}',
            '{$_GET['venice']}',
            '{$_GET['westchester']}',
    	'{$_GET['losangeles']}')
        ) OR 
        (mls_listings.zipcode LIKE '{$_GET['zipwestla']}'))
    	OR
    	(mls_listings.uniqueid LIKE '{$_GET['uniqueid']}')	
    	)
    	AND
        (mls_listings.bedrooms >= '{$_GET['beds']}') AND 
        (mls_listings.baths >= '{$_GET['baths']}') AND 
        (
            mls_listings.price >= '{$_GET['minprice']}' AND 
            mls_listings.price <= '{$_GET['maxprice']}'
    )
    ORDER BY mls_listings.price";
    Thanks for your time Dave.

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mmcknight View Post
    I searched the forums to no avail so if there is a previous post that covers this please let me know.
    gee, i did find another thread with an eerily similar setup... http://www.dbforums.com/mysql/165474...ing-query.html



    the problem you describe fits a certain scenario quite well -- you're using VARCHAR to store your numbers

    evidence supporting this hypothesis: "when it comes to the length of numbers that's where it's not coming through (1 vs. 10 vs. 100)."

    am i right?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2010
    Posts
    8
    Ha. I figured since it was a different topic / issue I should start a new thread.

    You are right on the money, I should have know it would be something obvious -- it always seems to be.

    What do you recommend storing them in, instead?

    Thanks for your help by the way.

  6. #6
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by mmcknight View Post
    What do you recommend storing them in, instead?
    well, since we are talking about houses, the price column probably doesn't need decimal places (cents), so INTEGER will do nicely, unless you have some properties going for more than 2 billion, in which case you can use BIGINT

    for number of bathrooms and similar columns, i would use TINYINT, which holds values up to 127
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  7. #7
    Join Date
    Mar 2010
    Posts
    8
    Works flawlessly!

    I really appreciate your help

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •