I have quite a complicated (for me anyway ) relational database for an apartments system I am developing. Keith (kickstart) was very helpful assisting me in creating the relationships and here is a general overview of the database structure
apartment Table Code: id address description
appliances Table Code: id applianceName
areas Table Code: id areaName
amenities Table Code: id amenitiesName
appliancesLink Code: id applianceId apartmentId
areaLink Code: id areaId apartmentId
amenitiesLink Code: id amenitiesId apartmentId
I want to have a form that lists all the appliances, amenities and areas as checkbox groups so the user can perform a search. For example; Find all apartments with fridge (appliance), tv (appliance) in london (area) or tokyo (area)
I have no idea where to start even , (ok probably with the form but in regards to the sql query )
if it helps heres the query I use to retrieve all the information for all apartments: Code: $result = mysql_query(" SELECT DISTINCT apartment.Id, apartment.Description, apartment.Address, apartment.Bathrooms, typeList, appliancesList, areaList, amenitiesList, businessList, picturesList FROM apartment LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(CONCAT_WS('~',typeid, typeName, typePriceNL, typePriceNH, typePriceML, typePriceMH) SEPARATOR ', ') AS TypeList FROM typeLink INNER JOIN type ON typeLink.typeId = type.Id GROUP BY apartmentId) typeSub ON apartment.Id = typeSub.ApartmentId LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(applianceName SEPARATOR ', ') AS AppliancesList FROM appliancesLink INNER JOIN appliances ON appliancesLink.applianceId = appliances.Id GROUP BY apartmentId) appliancesSub ON apartment.Id = appliancesSub.apartmentId LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(CONCAT_WS('~',areaName, areaDesc) SEPARATOR '* ') AS areaList FROM areaLink INNER JOIN areas ON areaLink.areaId = areas.Id GROUP BY apartmentId) areaSub ON apartment.Id = areaSub.apartmentId LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(CONCAT_WS('~',amenitiesName) SEPARATOR '* ') AS amenitiesList FROM amenitiesLink INNER JOIN amenities ON amenitiesLink.amenitiesId = amenities.Id GROUP BY apartmentId) amenitiesSub ON apartment.Id = amenitiesSub.apartmentId LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(CONCAT_WS('~',businessName) SEPARATOR '* ') AS businessList FROM businessLink INNER JOIN business ON businessLink.businessId = business.Id GROUP BY apartmentId) businessSub ON apartment.Id = businessSub.apartmentId LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(path SEPARATOR ', ') AS PicturesList FROM picturesLink INNER JOIN pictures ON picturesLink.pictureId = pictures.Id GROUP BY apartmentId) picturesSub ON apartment.Id = picturesSub.apartmentId WHERE apartment.Id = '".$apId."' ") or die(mysql_error());
I know this is not a simple question I really do appreciate people take time to help out! Any suggestions would really be appreciated.
