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
checkbox update
I am having a brain fart right now and i cant remember how to do this. if anyone could help that wou
Pls help with PHP
Pls can anyone direct me on how to create a user account for a customer. For example i want know if
IP question
ive got 2 ip addresses both global from same user how would i detect if they are local to each other
Issues: PHP Forms -Clearing
I've tried looking online before actually asking for help, but I've been looking for about the past
problem with script manager
Hi iam using Net.2005.I have created ajax enabledwebsite.i have master page in which i have script m
mysql select with $_get ?
Hi, i have this code:
Code: // If char id is 0 and character dont exist do:
if ($_GET["id
Regarding accessing SQL query issued by any user in Oracle 10g
Hi all,
i want to know the queries issued by various users accessing a database...
OPINIONS WANTED
This is my login page code, and I want your opinion on it please!
Code: // Login ~ CHECKS
getting Vars to pass to next page.
Hello all,
I have a confusing situation on my hands, i am a member of a gaming community and we
Internal class functions don't seem to get executed.
Hi. Can someone please put me out of my misery on this. I don't write much PHP and this has me baffl