Search with relational database
Posted on
16th Feb 2014 07:03 pm by
admin
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
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.
No comments posted yet
Your Answer:
Login to answer
215 25
Other forums
article site help remaining text
Hi all hope you will be fine
I am creating a article site in this site i want to put some text on
Why is this function returning a false value when it shouldn't be??
This is in an include file. I want it to check a value in an html form and see if it's just white s
Error when call dll from oracle
Hi all, please help me!
I have dll write C language, I want call it from oracle procedur
Parse XML
Good day,
I use oempro software and try to parse XMLReturn but it's not working for me, simpl
mr8m - reverse document
Friends,
I'm trying to reverse a document held by MIRO, but it reports the message balan
Something like an INI editor or a DelimitedText-Editor
Hi all,
Am very, very, very new to PHP and not sure if I should be posting this to a Javascri
Access website from only 1 computer...?
One of my customers wants his website to only be accessed by people in which they bought the website
how do i make a string??
hey guys,
can someone please tell me how to put data from the glob function into a string
Month String to Numeric?
Hi guys,
Given a month as a string, is there a simple way to find the numeric representation
parameter passing issue on function
Well I thought I had created a pretty slick web page, using ?page and $_GET to load pages which wher