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.
Auto fill in input value based on User_ID
Hello,I'm looking form some input on the following problem.User loads page -> page loads values from database and inputs them in boxes -> user edits input boxes -> new values
C++ API to Oracle dB
I need to perform a select command to the Oracle dB to obtain information from a table.What libraries can I link into my project to make this work?I read about the OCI - but I cannot find any
How to show the difference between two data field in a database with php.
Hello php gurus,how r u all... i'm not so well facing a typical problem please help me...i'm working with php and mysql.my problem is...in a data base have two integer field like parent_id &
UPLOAD IMAGES NOT WORKING!
Hi there i have a upload script in which it uploads the picture to a folder than creates two thumbnails in different folders however it is uploading the image and saving it in the folder /photos/ then
tell csv import script to ignore blank rows?
Hi i have the below script but i get an error if a row is blank ie a return in the csv file at the end i need to somehow tell it to skip the rows that have nothing on them:Code: <?php // define
Server side $_SESSION
how does one keep the session completely server side. no cookies to the browser at all. i need this site to be cookie and jscript free.
PHP arrays into arrays need help
HelloI am trying to highlight the days on my calendar based on the dates that i have in my database. Currently I can only get it to display the last element in the database which leads me to believe
Casting Decimals in Oracle
This code worked as a query in DB2, but I am not sure what the syntax is for casting decimals in Oracle:
upload photo limits
ive got a upload photo script and im just trying to make more secure currently it limits size and as you can see from my code below it limits only gif and php fileshow do i edit my code to limit all
Extract specific information from a PHP Multidimensional Array
Howdy! I've racked my brain over this for 24 hours now. I think it's time I asked for help. I'm learning PHP and believe my I've manipulated many code blocks trying to get this to work with for and