If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > MySQL > Querying Numbers from a Database

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old 03-24-10, 16:24
mmcknight mmcknight is offline
Registered User
 
Join Date: Mar 2010
Posts: 8
Question 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
Reply With Quote
  #2 (permalink)  
Old 03-24-10, 17:53
dav1mo dav1mo is offline
Registered User
 
Join Date: Dec 2007
Location: Richmond, VA
Posts: 782
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
Reply With Quote
  #3 (permalink)  
Old 03-24-10, 18:05
mmcknight mmcknight is offline
Registered User
 
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.
Reply With Quote
  #4 (permalink)  
Old 03-24-10, 20:32
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
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... Optimizing a Query



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?
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #5 (permalink)  
Old 03-24-10, 20:42
mmcknight mmcknight is offline
Registered User
 
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.
Reply With Quote
  #6 (permalink)  
Old 03-24-10, 20:49
r937 r937 is offline
SQL Consultant
 
Join Date: Apr 2002
Location: Toronto, Canada
Posts: 19,525
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
__________________
r937.com | rudy.ca
please visit Simply SQL and buy my book
Reply With Quote
  #7 (permalink)  
Old 03-24-10, 23:39
mmcknight mmcknight is offline
Registered User
 
Join Date: Mar 2010
Posts: 8
Works flawlessly!

I really appreciate your help
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On