Conditionals in query?

Databases
1

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:


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:


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!

2

price >= 100000
price <= 200000
(price >= 100000 AND price <= 200000)

3

Hi oddz. Thank you for the reply. Maybe I didn’t explain myself good enough :frowning: 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!

4

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…

[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…

[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 :slight_smile: