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.
Creating XML with php
I need to creat an XML with php and have successfully produced a valid output. The problem I have is that the XML requirements of the application that will import the XML requires that the XML file
Need some ideas as to how to go about sorting this array...
Here's how the array's are created..Code: <?phpwhile ($x = readdir($dp)) { $order[] = $p['order']; $files[] = $x;}?>Here's a sample of how they come out..Code: Array( [0]
upload form file types....
Hey all, I am learning PHP and I am writing a script from the W3C Schools tutorials for uploading files to my server. I want to be able to use it for a friend to upload files to my server (I know to
Delete records not in top 15
Hi,I'm creating a hall of fame page in my game and the page will only display the top 15 scores.So I want to delete the scores that get bumped down the list.How would I write the query to delete
Create or replace package is ending ORA-21700
Database 9.2.0., patch 8 (9.2.0.8.0).
Taking an HTML form and a PHP program and making it into one working file.
Hello, So I would like to display everything INSIDE a specific directory. In other words, I would like to display all file name's inside all subfolders in a specific directory. (NOT the subfolder
cURL and Sessions
Ohai.So, I'm trying to cURL a bunch of things off of a page, and put them into a session. I've added all of the items so they look like $_SESSION['fur'] = ge_item(6814)Now I just need to figure out
Do not allow posting of whitespace
Currently the script below works if the user does not type a name/message, but if i create a whitespace with the spacebar, it will submit. How would I fix this?message page:<?php// Get the
Internal class functions don't seem to get executed.
Hi. Can someone please put me out of my misery on this. I don't write much PHP and this has me baffled. I've probably work myself into the wrong mindset with the time I've spent on this. What's
Record count of entire database
Does anyone know if there is a way to obtain the number of records in each table of the database using a single select statement or must a select count be done for each individual table?
