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.
Data Function is Its Not working IN IE8
This is my first time to use formums. I hope i can get solution for this problem.
PRotect my POST DATA... FROM HTML ATTACKS
I need to like let's sayIf a user submit's his Prayer and he put's <meta refresh stuff while posting his Prayer, I need to disable that and replace it with some other text... so it doesn't
Loop Through Date Range
Hi guys,
How to insert random unique values in 2 columns ?
I have table with 2 columns
Logic question
im wondering what's the best method to do the above list:it is for alliance @ MMORPG game -the administrator of alliance have 10 slots for creating ranks ... each rank have it's own permissions (in
PHP and MySQL on intranet?
Is it possible to use both these on an intranet network?
While Problem
i am having a problem with a while statement here is the code Code:
Oracle Text CTX_DOC.snippet slow
I have a table (FILE_TABLE) that contains a blob column (ft_file) and I have created the following Oracle Text Index on that table/file column....
if statements problems
Hi. I'm trying to make a web form, but I kind of hit a dead end trying to figure out why it doesn't work. Basically, I'm trying to read from a file and then populate the form according to the file.The
News script
Hi ...I am not sure if this is the rite place to post this but if its not sorry i am new here and need help with my cricket scores script now the code works well i just cant get the next or prev to