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

Need some ideas as to how to go about sorting this array...
Here's how the array's are created..

Code: <?php
while ($x = readdir($dp)) {

links using header()
Hi All

I'm not sure where to ask for help on this but I hope someone can offer some. I'm at

array ...
hi
Code: function formatCategories($categories, $parentId)
{
// $navCat stores all child

Character Sets/Collations Stuff
Can someone please give me a check list of things I must do to setup all the charset stuff for my ph

About imagecopyresampled()
Hello,

I am looking to use this function to resize parts of an image to a fixed thumbnail siz

New to mysqli library - Multiple query problem
Greetings,

I am writing a batch program that executes 3 queries on a single page. Using mysql

Array question
Does anyone know how to create a program that takes the $ amount for 8 different departments and gi

Login Functionality Working Different on IE
This is an odd one, I have a site which has an admin section. The admin pages unsurprisingly require

problem with php mysql query
Hi guy's...

I'm totally lost here..because don't have any idea how to make a query for grab r

Call to undefined function mysql_fetch_accoc()
New to php/mysql coding. What's wrong with this sequence, or am I
just not seeing to obvious?