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 Like 25 Dislike
Previous forums Next forums
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

Sign up to write
Sign up now if you have flare of writing..
Login   |   Register
Follow Us
Indyaspeak @ Facebook Indyaspeak @ Twitter Indyaspeak @ Pinterest RSS



Play Free Quiz and Win Cash