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.

Other php-forum

reorder sql query
ok so i have this very simple code:
Code: $query = "SELECT search_keywords, COUNT(search_key

multipart emiail forms
Hi All,

I am new to the boards and I've been working on a form (which is rather massive, imo

Multidimensional Array into an Html table, help!
Hello all, I am new to HTML/PHP so any help would be great.

I have a multidimensional array f

Can anyone give me some link on .htaccess tutorial
i wish to have friendly url using .htaccess, but no idea yet about this

Hope that anyone cou

Help, Base64 encoding url and decoding server side.
Hello everyone, i have a website with videos and games and also provide an embed code which people c

utf8_general_ci error ??
I have all kinds of data to be inserted in database table

//I am getting this error

Yo

Generate multilayered array from string.
ok so i have a string that looks like this:
Code: [Select]blog:edit_all,delete_all|users:edit_all

Generate multilayered array from string.
ok so i have a string that looks like this:
Code: blog:edit_all,delete_all|users:edit_all,delete_

how to easy edit text, with box? Help.
Hi.
I have a little problem. I'm doing a webpage for my aunt and I would like to make it as easy

Structure Question - One Table or One Table Per Record Set?
I have a web app (mySQL and PHP) which allows people to create an item with up to 200 records which