please help - Join query

[deedee]

Hi, i hope someone can help with this.

I have 4 tables set up like this

[u]item[/u]
id

[u]season [/u]
id
itemid
date

[u]room[/u]
id
itemid
name
capacity

[u]season_room[/u]
id
seasonid
roomid

[u]rate[/u]
id
season_roomid
rate
min_nights

..And I need to display them on a table with the following headings


                                01 Apr - 31 Mar   |  01 Jun 06 - 30 Nov 06   |01 Dec 06 - 31 Mar

Room Type | Max Cap| Min nights |Rate   |Min nights  |Rate            |Min nights    |Rate
 

I have the following code but the table is coming up blank

//Get data to fill table
$q = mysql_query("SELECT * FROM room LEFT JOIN season_room ON  season_room.roomid= room.id AND room.itemid='$item'");
while ($r = mysql_fetch_array($q))
{
$room = $r[room.id];
$srid = $r[season_room.id];
echo '<tr><td>'.$r[room.name].'</td><td>'.$r[room.capacity].'</td>';

$q = mysql_query("SELECT * FROM rates WHERE season_roomid='$srid' ");
while ($r = mysql_fetch_array($q))
{
echo '<td>'.$r[min_nights].'</td><td>'.$r[rate].'</td>';
}
echo '</tr>';
}


Can anyone advise the best select statement/s and loops to use?

Any help would be greatly appreciated.  ;D
Can give more information if needed

[fenway]

Well, your hash key names are invalid -- they don't come back with a table prefix; you'll need to alias these columns yourself.

[shivabharat]

Hi,

Please make sure you use the code tags when you post anything. Its becomes very hard for us to go thorugh your posting.

[deedee]

hi Guys,

thanks for your comments. sorry about the code tags. I've changed the code and it's now filling the table to a certain point, but just not right yet. http://www.tropicalexperience.com.au/info.php?seeinfo=2139

<php?
$q = mysql_query("SELECT season_room.id, room.name AS rname, room.capacity AS rcap, rates.min_nights As rnts, rates.rate FROM room LEFT JOIN season_room ON season_room.roomid = room.id LEFT JOIN rates ON season_room.id = rates.season_roomid WHERE room.itemid = '$item'");
while ($r = mysql_fetch_array($q))
{
echo '<tr><td>'.$r[rname].'</td><td>'.$r[rcap].'</td>';
echo '<td>'.$r[rnts].'</td><td>'.$r[rate].'</td></tr>';
}
?>

Can anyone see a better way of doing this? I've spent ages at it now and just don't seem to be getting anywhere fast.

[fenway]

I'm not sure I understand how that table is being populated.