Table relationships

[rvdb86]

Hi,

 

I am developing an apartment catalogue application. Each apartment has the following fields:

• Address
  
• Description
  
• Amenities
  
• type
  
• Appliances
  

 

As the fields; Amenities, type and appliances will be duplicated for the apartments I put them in separate tables, so for example the appliances table will hold all the appliances and each appliance that is applicable will be related to the apartment by ID. The same goes for the other two fields.

 

I was hoping someone could help me with building the relationships between the apartment, type, appliances etc..

 

I would very much appreciate any suggestions!

[kickstart]

Hi

 

I presume you intend to have one table for an apartment, and one table each for the a list of the possible amenities / type / appliances.

 

In which case you just have 3 more tables to connect an appartment to the amenities / type / appliances that apply to it.

 

So you would have:-

 

Amenities

Id

Description

 

Type

Id

Description

 

Appliances

Id

Description

 

Apartment

Id

Description

Address

 

Then

 

AmenitiesLink

Id

AmenitiesId

ApartmentId

 

TypeLink

Id

TypeId

ApartmentId

 

AppliancesLink

Id

AppliancesId

ApartmentId

 

So to get all the apartments with (say) a dish washer that is a studio and has a swiming pool you would use something like:-

 

SELECT DISTINCT Apartment.Id, Apartment.Description, Apartment.Address

FROM Apartment

INNER JOIN AmenitiesLink ON Apartment.Id = AmenitiesLink.ApartmentId

INNER JOIN Amenities ON AmenitiesLink.AmenitiesId = Amenities.Id AND Amenities.Description = "swiming pool"

INNER JOIN TypeLink ON Apartment.Id = TypeLink.ApartmentId

INNER JOIN Type ON TypeLink.TypeId = Type.Id AND Type.Description = "studio"

INNER JOIN AppliancesLink ON Apartment.Id = AppliancesLink.AppliancesId

INNER JOIN Appliances ON AppliancesLink.AppliancesId = Appliances.Id AND Appliances.Description = "dish washer"

 

All the best

 

Keith

[rvdb86]

great, looks like exactly what I need!

 

I am still at the planning stage but when I implement this I will let you know how it goes.

 

Thanks for your time, its greatly appreciated!

[rvdb86]

Hi kickstart,

 

I just implemented your suggestions. However i have a question.. with this table structure how would i print a list of all apartments with their type / amenities / appliances?

 

TIA!

[kickstart]

Hi

 

Depends

 

If you wanted to do it with a conventional JOIN then the problems is that you could have 1 type, 5 appliances and 10 amenities, so trying to bring back a line with them all would be difficult.

 

Easiest way is probably to use GROUP_CONCAT to bring back a single field for each property listing the type, same for the appliances and same for the amenities. Down side is that if you then want them listed seperatly it becomes less elegant (easy to do, just not elegant code).

 

Example (excuse any typos, hope it gives you the right idea):-

 

SELECT DISTINCT Apartment.Id, Apartment.Description, Apartment.Address, AmenitiesList, TypeList, AppliancesList
FROM Apartment
LEFT OUTER JOIN (SELECT ApartmentId, GROUP_CONCAT(Description 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(Description 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(Description SEPARATOR ', ') AS AppliancesList FROM AppliancesLink INNER JOIN Appliances ON AppliancesLink.AppliancesId = Appliances.Id GROUP BY ApartmentId) appliancesSub ON Apartment.Id = appliancesSub.ApartmentId

 

If you then wanted a separate list of the amenities (ie, say you wanted each amenity to be a link to a description of that amenity) you would have to use explode on the AmenitiesList field in php.

 

Hope this helps.

 

All the best

 

Keith

[rvdb86]

Ok thanks for the suggestion, however I ran into a problem when i tried implementing it.

 

I am also not to sure how to print the results as i have never used GROUP_CONCAT before

here is my code: (note that i am not using amenities to try keep things simple, ill will add it later)

$result = mysql_query('
SELECT DISTINCT apartment.Id, apartment.Description, apartment.Address, typeList, appliancesList
FROM apartment
LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(Description 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(Description SEPARATOR ', ') AS AppliancesList FROM appliancesLink INNER JOIN appliances ON appliancesLink.appliancesId = appliances.Id GROUP BY apartmentId) appliancesSub ON apartment.Id = appliancesSub.apartmentId
') or die(mysql_error());

while($row = mysql_fetch_array($result))
			{
			explode(',', $row['apartment.Id']);
			}

			mysql_close($con);

 

I get the following error:

Warning: Wrong parameter count for mysql_query()

 

Thanks for taking the time to try help, much appreciated!

[kickstart]

Hi

 

First thing, I left the DISTINCT in there when editing. Remove that .

 

Second problem is that you have surround the SQL with single quotes when it also contains a couple of single quotes. Change the outer ones to double quotes and it should be fine.

 

Lastly you need to explode the lists into an array and then loop around the array to output them:-

 

<?php
$result = mysql_query("
SELECT apartment.Id, apartment.Description, apartment.Address, typeList, appliancesList
FROM apartment
LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(Description 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(Description SEPARATOR ', ') AS AppliancesList FROM appliancesLink INNER JOIN appliances ON appliancesLink.appliancesId = appliances.Id GROUP BY apartmentId) appliancesSub ON apartment.Id = appliancesSub.apartmentId
") or die(mysql_error());

while($row = mysql_fetch_array($result))
{
$Types = explode(', ', $row['typeList']);
foreach($Types AS $Type)
{
	echo "$Type <br />";
}
$Appliances = explode(', ', $row['appliancesList']);
foreach($Appliances AS $Appliance)
{
	echo "$Appliance <br />";
}
}
mysql_close($con);	
?>

 

If you do want to use explode in this way then it would be best to use a delimiter in the GROUP_CONCAT and the explode that isn't likely to be in any of the descriptions (a comma is too commonly used).

 

All the best

 

Keith

[rvdb86]

thanks kickstart it's working great!

 

except for one thing i don't understand how to do it (sorry  :-\)

for example the apartment types table stores the name of the type e.g. studio and the price. With the code you kindly provided me with when I out put the exploded typeList all the filed (name and price) are together.

 

How would i be able to separate these?

 

you've helped a lot up to now and its greatly appreciated and I hope this will be the last thing i need to bother you with! 

[kickstart]

Hi

 

Think I am confused about the issue you are having now. Could you post the code you are currently using, and the format you want output.

 

All the best

 

Keith

[rvdb86]

Hey sorry about the confusion.

 

Heres the code I am using:

$result = mysql_query("
SELECT apartment.Id, apartment.Description, apartment.Address, typeList, appliancesList
FROM apartment
LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(typeName, typePrice 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
") or die(mysql_error());

while($row = mysql_fetch_array($result))
{
echo "<strong>".$row['Address']."</strong><br />";

$Types = explode(', ', $row['typeList']);
foreach($Types AS $Type)
{
	echo "$Type <br />";
}
$Appliances = explode(', ', $row['appliancesList']);
foreach($Appliances AS $Appliance)
{
	echo "$Appliance <br />";
}
}				
			mysql_close($con);
			?>

 

The above code outputs this:

5nd. Street
Small Studio500 
Stove 
Microwave 

 

If you notice the second line of the output "Small Studio500", where small studio is the type and 500 is the price, they are not separated.

 

I need a way of separating the values in $Type and i have no idea where to start with that 

[kickstart]

Hi

 

Problem appears to be this line:-

 

GROUP_CONCAT(typeName, typePrice SEPARATOR ', ')

 

Not sure what type price is (ie, appears odd to have a price for a type of apartment). However if it is something you want on each line then I think the only way to do it is slightly messy, and put a delimiter in the type field and then explode that as well:-

 

$result = mysql_query("
SELECT apartment.Id, apartment.Description, apartment.Address, typeList, appliancesList
FROM apartment
LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(CONCAT_WS('~',typeName, typePrice) 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
") or die(mysql_error());

while($row = mysql_fetch_array($result))
{
echo "<strong>".$row['Address']."</strong><br />";

$Types = explode(', ', $row['typeList']);
foreach($Types AS $Type)
{
	$TypeSplit = explode ('~',$Type);
	echo 'Type Name '.$TypeSplit[0].' Type Price '.$TypeSplit[1].' <br />;
}
$Appliances = explode(', ', $row['appliancesList']);
foreach($Appliances AS $Appliance)
{
	echo "$Appliance <br />";
}
}				
mysql_close($con);
?>

 

You will probably want to clean this up and use more appropriate delimiters, but hopefully gives you an idea.

 

All the best

 

Keith

[rvdb86]

Keith once again you have saved me.

 

Thanks a lot it is greatly appreciated!

[rvdb86]

Hey Keith, sorry to bother you again.

 

I have made some good progress on my own using the code you have provided me with so far and even managed to create different table links.

 

Here is one select query that links the apartment table to the bookings table and returns apartments that are available on a date range selected by the user:

SELECT DISTINCT apartment.Id, apartment.Address
FROM apartment
INNER JOIN bookingsApartmentLink ON apartment.Id = bookingsApartmentLink.apartmentId
INNER JOIN bookings ON bookingsApartmentLink.bookingId = bookings.Id AND 
bookings.startDate > "'.$startDateTmp.'" AND bookings.startDate > "'.$endDateTmp.'" || 
bookings.endDate <  "'.$startDateTmp.'" AND bookings.endDate < "'.$endDateTmp.'"

 

This works great and shows all apartments available between a start date and end date. However I want the user to select an apartment and check only the availability of that apartment. I tried the following which did not work and was hoping you may have a suggestion:

SELECT DISTINCT apartment.Id, apartment.Address
FROM apartment WHERE apartment.Id = "'.$_POST['apId'].'"
INNER JOIN bookingsApartmentLink ON apartment.Id = bookingsApartmentLink.apartmentId
INNER JOIN bookings ON bookingsApartmentLink.bookingId = bookings.Id AND 
bookings.startDate > "'.$startDateTmp.'" AND bookings.startDate > "'.$endDateTmp.'" || 
bookings.endDate <  "'.$startDateTmp.'" AND bookings.endDate < "'.$endDateTmp.'"

 

Thanks in advanced!

[kickstart]

Hi

 

The WHERE needs to be at the end, as it refers to the results of the JOINS, not a particular table that you are about to JOIN:-

 

SELECT DISTINCT apartment.Id, apartment.Address
FROM apartment 
INNER JOIN bookingsApartmentLink ON apartment.Id = bookingsApartmentLink.apartmentId
INNER JOIN bookings ON bookingsApartmentLink.bookingId = bookings.Id 
WHERE apartment.Id = "'.$_POST['apId'].'"
AND (bookings.startDate > "'.$endDateTmp.'" 
OR bookings.endDate <  "'.$startDateTmp.'" )

 

Also moved the date check into the WHERE clause, and simplified it a touch.

 

All the best

 

Keith

[rvdb86]

Thanks alot keith. This is a great way of learning for me.. hopefully not to tedious for you 

[rvdb86]

Hey Keith its me again (sorry)... I hope you have some spare time to help me as kindly as you usually do.

 

I am trying to randomly select a row from the database results where the type field matches the users input. Heres my code:

$offset_result = mysql_query( " SELECT FLOOR(RAND() * COUNT(*)) AS offset FROM apartment LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(CONCAT_WS('~',typeName, typePriceNL, typePriceML) SEPARATOR ', ') AS TypeList FROM typeLink INNER JOIN type ON typeLink.typeId = type.Id GROUP BY apartmentId) typeSub ON apartment.Id = typeSub.ApartmentId WHERE type.Id = '".$type."'");
$offset_row = mysql_fetch_object( $offset_result ); 
$offset = $offset_row->offset;
$result = mysql_query( " SELECT * FROM apartment LIMIT $offset, 1 " ) or die(mysql_error());

 

However I get the following error message:

Warning: mysql_fetch_object(): supplied argument is not a valid MySQL result resource in ../tmp/functions.php on line 109

 

Notice: Trying to get property of non-object in ../tmp/functions.php on line 110

 

Any suggestions would be great!

 

[fenway]

Check mysql_error() after the first query and you'll see the problem.

[rvdb86]

Here I am again.  :'(

 

I am running into bit of a problem and I hope someone knows a solution.

 

I have all my relationships setup correctly and everything was working fine until... I tried to create this function that checks the availability of an apartment by checking the chosen dates against dates stored in the bookings table.

 

the problem is that if there were no previous bookings for an apartment the code thinks it is not available.

heres my code:

SELECT apartment.Id, apartment.Description, apartment.Address, typeList, appliancesList
FROM apartment
LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(CONCAT_WS("~",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
INNER JOIN bookingsApartmentLink ON apartment.Id = bookingsApartmentLink.apartmentId
INNER JOIN bookings ON bookingsApartmentLink.bookingId = bookings.Id AND  
bookings.startDate > "'.$startDateTmp.'" AND bookings.startDate > "'.$endDateTmp.'" || 
bookings.endDate <  "'.$startDateTmp.'" AND bookings.endDate < "'.$endDateTmp.'"

 

Ideally I need a way to check if the apartmentId exists in the bookingsApartmentLink table. Anyone know how this can be done?

 

I really do appreciate the time people take to help!

[kickstart]

Hi

 

Probably best to change the last INNER JOIN to a LEFT OUTER JOIN.

 

All the best

 

Keith

[rvdb86]

Hi Keith, thanks for the suggestion, however the script still only selects apartments that already have a booking.

It does not take into account any apartments that are not related to a booking and therefore do not have an entry in the bookingsApartmentLink table.

[kickstart]

Hi

 

Think it needs the LEFT OUTER JOIN on the previous one as well, but to take a step back and look at the problem you have.

 

Problem with your existing code is that for the JOINS to work there must be a matching record on the bookingsApartmentLink  and bookings tables.

 

Should be a way round this, but not 100% certain what you are trying to find just from that code.

 

All the best

 

Keith

[rvdb86]

Hey Keith sorry if i didnt explain the problem clearly, and thanks for taking the item.

 

The whole idea of the system i am trying to develop is to book apartments.

 

i have an apartments table and booking table that are linked by bookingsApartmentLink.

 

The bookings table holds the dates for each booking.

 

I want the user to choose two dates (start date and end date) to see all the apartments availible during those two dates.

 

I thought i could do that by comparing the dates the user chose and the dates in the booking table, but the problem is that this will only show the apartments that already have bookings, and if an apartment has had no previous bookings it will not show it as available. 

I understand the problem, that if the apartment has had no previous booking it will not be in the bookingsApartmentLink table and therefore the code i have wont count it. I just don't know how to overcome this 

 

I would be extremely grateful if you have any ideas.

 

[kickstart]

Hi

 

Think you need a list of all the apartments that do not have a booking for that week. Think this could be got with the following SQL.

 

SELECT bookingsApartmentLink.apartmentId
FROM bookingsApartmentLink
INNER JOIN bookings 
ON bookingsApartmentLink.bookingId = bookings.Id 
WHERE ((bookings.startDate < "'.$startDateTmp.'" AND bookings.endDate > "'.$endDateTmp.'" )
|| (bookings.startDate >  "'.$startDateTmp.'" AND bookings.startDate < "'.$endDateTmp.'")
|| (bookings.endDate >  "'.$startDateTmp.'" AND bookings.endDate < "'.$endDateTmp.'"))

 

What you need is to join that with the first part of your other SQL and look for items with a NULL bookingsApartmentLink.apartmentId.

 

Doing this using the above as a subselect to make it more obvious what is happening:-

 

SELECT apartment.Id, apartment.Description, apartment.Address, typeList, appliancesList
FROM apartment
LEFT OUTER JOIN (SELECT apartmentId, GROUP_CONCAT(CONCAT_WS("~",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 bookingsApartmentLink.apartmentId
FROM bookingsApartmentLink
INNER JOIN bookings 
ON bookingsApartmentLink.bookingId = bookings.Id 
WHERE ((bookings.startDate < "'.$startDateTmp.'" AND bookings.endDate > "'.$endDateTmp.'" )
|| (bookings.startDate >  "'.$startDateTmp.'" AND bookings.startDate < "'.$endDateTmp.'")
|| (bookings.endDate >  "'.$startDateTmp.'" AND bookings.endDate < "'.$endDateTmp.'"))) bookingsApartmentSubselect
ON apartment.Id = bookingsApartmentSubselect.apartmentId
WHERE  bookingsApartmentSubselect.apartmentId IS NULL

 

All the best

 

Keith

[rvdb86]

Keith, you have no idea how much you have helped me... yet again!

 

This works perfectly and you are a true gentleman for taking your time to help!

you are an asset to this forum.

[rvdb86]

Hey keith, I guess you are probably sick of seeing this thread but I was hoping you have some spare time to help me out on one more thing.

 

I am trying to create a search function for the apartments base on all their elements, type, area, appliances etc.

 

So far I have created the form (the easy part) but I dont really know where to start with searching the joined tables.

I thought maybe to make use of the IN clause but I have never used it before and not sure this is the correct way, I also tried creating a multidimensional array of all the apartments and compare the values posted from the form against the array, but again I don't know if this is the best way.

 

Here is my form:

<fieldset> 
                    	<label for="apType">Apartment Type: </label> 
                        <select name="apType" id="apType"> 
                        	<option value="0"> </option> 
                       	  <option value="7">Studio</option> 
<option value="8">One bedroom</option> 
<option value="9">Two bedroom</option> 
                        </select> 
                        <br /> 
                        <label for="apArea">Apartment Location: </label> 
                        <select name="apArea" id="apArea"> 
                        	<option value="0"> </option> 
                       	  <option value="4">Upper West Side (west 70th and west 71st Street)</option> 
<option value="5">Upper East Side (east 80th and east 81st street)</option> 
<option value="6">Midtown West (west 47th street)</option> 
                        </select> 
                        <br /> 
                        <label for="numGuests">Number of Guests: </label> 
                        <select name="numGuests" id="numGuests"> 
                        	<option value="0"> </option> 
                   	                                <option value="1">1</option> 
                                                        <option value="2">2</option> 
                                                        <option value="3">3</option> 
                                                        <option value="4">4</option> 
                                                        <option value="5">5</option> 
                                                        <option value="6">6</option> 
                                                    </select> 
                        <br /> 
                        <label for="numBathrooms">Number of Bathrooms: </label> 
                        <select name="numBathrooms" id="numBathrooms"> 
                        	<option value="0"> </option> 
                        <option value="1">1</option> 
                            <option value="2">2</option> 
                            <option value="3">3</option> 
                        </select> 
                        <br /> 
                        
                        Amenities:
                        <input type="checkbox" value="1" name="amenities[]" id="Air Conditioning"><label for="Air Conditioning">Air Conditioning</label> 
<input type="checkbox" value="2" name="amenities[]" id="Telephone"><label for="Telephone">Telephone</label> 
                        <br /> 
                        Appliances:
                        <input type="checkbox" value="1" name="appliance[]" id="Microwave"><label for="Microwave">Microwave</label> 
<input type="checkbox" value="2" name="appliance[]" id="Stove"><label for="Stove">Stove</label> 
<input type="checkbox" value="3" name="appliance[]" id="Full Refrigerator"><label for="Full Refrigerator">Full Refrigerator</label> 
<input type="checkbox" value="4" name="appliance[]" id="Iron"><label for="Iron">Iron</label> 
<input type="checkbox" value="5" name="appliance[]" id="Ironing Board"><label for="Ironing Board">Ironing Board</label> 
                        <br /> 
					Business Tools: 
                        <input type="checkbox" value="1" name="business[]" id="WIFI"><label for="WIFI">WIFI</label> 
                    	<input type="submit" name="advancedSearch" value="Search" /> 
                    </fieldset> 

 

I hope I am not asking to much, I just have not been able to find any examples/tutorials or answer for this type of structure.

 

Thanks in advanced for any suggestions you can offer!