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.

Your Answer:

Login to answer

Other forums

newbie question
Hi out thereIm totaly new in this forum and to .net and vb so here is a totaly newbie question.I wan

Problem with a select distinct
I have a problema with a select distinct, I have the next sql:

select distinct a,b,c,d fr

WM transfer order: confirm different batch than proposed by system
Hi folks,

following scenario: we have a full WM with mixed batch numbers allowed in stora

help retrieiving results and doing pagination
Having some trouble trying to get the results to show on more than just one page.

What is ha

Help me with some material on Open Text Overview.
Hi Experts,
Please help me out with some material on Open Text. I have been supporting AP Workf

Help with PHP Calendar code...
Hello, I'm new to this forum and I'm glad I found it.
I wrote this code for a PHP calendar as an

php/xmlrpc class issue
I am working on xml-rpc in php to start with.

I have a class that only has variables defined

Code Review - SQL and Insertion Attacks (Warning: Not for Newbs)
Hey guys,

Its been a while, I know. Use to love coming here to answer peoples questions, but

Need help making a blockquote and line items conditional
I have some code I bought a few years ago that allows my clients to update content on their site usi

MySQL Does not UPDATE- SQLString Problem
vb Syntax (Toggle Plain Text) 1. SQL = "UPDATE sampletable SET column1 = 'C1sample1'"