SitePoint Sponsor

User Tag List

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

    Combining 3 records & grouping by price

    I'm not sure if the title is formulated the right way but what i want is the following/ I'm working on a real estate website. The important tables in are:

    Code MySQL:
    CREATE TABLE IF NOT EXISTS `properties` (
      `property_id` int(8) unsigned NOT NULL auto_increment,
      `type_id` int(2) NOT NULL default '0',
      `property_type_id` int(3) NOT NULL default '0',
      `county_id` int(3) NOT NULL default '0',
      `city_id` int(3) NOT NULL default '0',
      `district_eng` varchar(128) default NULL,
      `district_gr` varchar(128) default NULL,
      `plot_size` varchar(64) default NULL,
      `living_space` varchar(64) default NULL,
      `rooms` int(3) default NULL,
      `bedrooms` int(2) default NULL,
      `bathrooms` int(2) default NULL,
      `price` float(9,2) default NULL,
      `offer_price` float(9,2) default NULL,
      PRIMARY KEY  (`property_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=1 ;
     
    CREATE TABLE IF NOT EXISTS `property_types` (
      `property_type_id` int(3) NOT NULL auto_increment,
      `property_type_eng` varchar(128) default NULL,
      `property_type_gr` varchar(128) default NULL,
      PRIMARY KEY  (`property_type_id`)
    ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
    The table is holding the following records:

    (Apartments, Bungalows, Maisonettes, Offices, Off plan Properties, Plots, Shops and Villas) The table is an important factor for the search facility, but the owners would like to use these records as quick links to the property listings as well and this is where my question is about. Although the records from this table are separately used in the search form (drop down list) for the quick links they would like to combine Bungalows, Maisonettes and Villas into one link. Is this somehow possible to create something like that inside a query.

    They further more would like to group the links by price, I.E.

    Up to 100.000
    101.000 - 200.000
    201.000 - 300.000
    etc...

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

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,021
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    Quote Originally Posted by donboe View Post
    ... for the quick links they would like to combine Bungalows, Maisonettes and Villas into one link.
    i'm having a real hard time relating this requirement to your table layouts

    also, what's a link? which column?

    by the way, INT(2) and INT(3) don't do what i think you wanted them to do -- these are exactly the same sized columns as INT(8) and INT(937)

    look into using TINYINT and SMALLINT instead
    r937.com | rudy.ca | 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
  •