SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Conditionals in query?

    I need to create a results query for a simple (property website) search form and I'm struggling with the price field somehow! The search form has just four criteria (property_type, property_location, price_from and price_to) and it's the last two of which I'm not sure how to handle them inside the query. This is the properties table:

    Code MySQL:
    CREATE TABLE IF NOT EXISTS `properties` (
      `property_id` int(8) unsigned NOT NULL auto_increment,
      `type_id` smallint(2) NOT NULL default '0',
      `property_type_id` smallint(2) NOT NULL default '0',
      `county_id` smallint(2) NOT NULL default '0',
      `city_id` smallint(2) NOT NULL default '0',
      `district_eng` varchar(128) default NULL,
      `district_gr` varchar(128) default NULL,
      `plot_size` varchar(128) default NULL,
      `living_space` varchar(128) default NULL,
      `rooms` smallint(2) default NULL,
      `bedrooms` smallint(2) default NULL,
      `bathrooms` smallint(2) default NULL,
      `price` float(9,2) default NULL,
      PRIMARY KEY  (`property_id`)
    ) ENGINE=InnoDB  DEFAULT CHARSET=utf8;

    The two mentioned form fields are simple text fields where visitors can fill in a min. price and a max. price. When both fields are indeed filled in I could handle it inside my query wit a BETWEEN statement:
    Code MySQL:
    WHERE price BEWTEEN 100000 AND 200000
    But what would be the best way to handle a situation where neither of the fields where filled in or just one of the two, either price_from or price_to?

    Thank you in advance!
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  2. #2
    SitePoint Wizard bronze trophy
    Join Date
    Jul 2006
    Location
    Augusta, Georgia, United States
    Posts
    4,151
    Mentioned
    16 Post(s)
    Tagged
    3 Thread(s)
    price >= 100000
    price <= 200000
    (price >= 100000 AND price <= 200000)
    The only code I hate more than my own is everyone else's.

  3. #3
    SitePoint Wizard donboe's Avatar
    Join Date
    Jun 2010
    Location
    Netherlands
    Posts
    2,114
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Quote Originally Posted by oddz View Post
    price >= 100000
    price <= 200000
    (price >= 100000 AND price <= 200000)
    Hi oddz. Thank you for the reply. Maybe I didn't explain myself good enough My question was/is more related to if it is possible to handle the conditionals within mySql or that I should I handle this with my server side scripting (Coldfusion in my case)?

    Sorry for the confusion!
    “Good artists copy, great artists steal” (Pablo Picasso - 1934)

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,273
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by donboe View Post
    But what would be the best way to handle a situation where neither of the fields where filled in or just one of the two, either price_from or price_to?
    let's take these one at a time

    when no price is entered in either form field, you simply omit the price condition from your WHERE clause completely, i.e. returning properties regardless of price

    when only the price_to form field is filled in, the user most likely doesn't want to see any properties with a higher price, so you would use...
    Code:
    [WHERE] ...
       AND price <= #form.price_to#
    when only the price_from form field is filled in, the user most likely doesn't want to see any cheaper properties, so you would use...
    Code:
    [WHERE] ...
       AND price >= #form.price_from#
    by the way, FLOAT(9,2) is not optimal for properties

    when was the last time you saw a house listed with a price that includes pennies?

    use INTEGER
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

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