rvdb86 Posted November 9, 2009 Share Posted November 9, 2009 Hey, 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 id address description appliances Table id applianceName areas Table id areaName amenities Table id amenitiesName appliancesLink id applianceId apartmentId areaLink id areaId apartmentId amenitiesLink 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: $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. Link to comment https://forums.phpfreaks.com/topic/180844-search-with-relational-database/ Share on other sites More sharing options...
Recommended Posts
Archived
This topic is now archived and is closed to further replies.