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

How to show more than 1 users with this code...
Hello,
i have a table that shows users only if I, as Administrator, want to be shown. But its sho

Question about ShowWindow()
Hello....

I'm struggling with sumpin, and I hope y'all can help.

I've got a CFormView

problem with php server update from mid 2009
Hi,

I have this navigation menu on 2 websites which used to work just fine. After a recent up

Insert data in Mysql and move to another page
I'm stuck with this simple problem. I need to insert data in Mysql and then hit submit and move to a

SWF image using php?
Hello once again.

My latest en devour requires me to produce an image of a static .swf that i

Checking if variable is 0 as opposed to NULL/Empty...
I'm trying to write some code that will retrieve a user's access level from my database and if it do

if description does not contain the following words
Hello everyone,

I am parsing a MS Excel (.xls) file and adding it's content to a mySQL databa

Problem with shopcart code
Hello, I am having a bit of trouble being able to add a product to my shopcart. My mysql database i

How do I get the row number from from an sql table query
Hi all

I have a table that I query and it returns a number of rows.
mysql_num_rows($query)

need help in creating captcha
hi i have a problem creating captcha system. i create some basic script for image displaying but it