Jump to content


Photo

please help - Join query


  • Please log in to reply
4 replies to this topic

#1 deedee

deedee
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 30 September 2006 - 05:41 AM

Hi, i hope someone can help with this.

I have 4 tables set up like this

item
id

season
id
itemid
date

room
id
itemid
name
capacity

season_room
id
seasonid
roomid

rate
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


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 30 September 2006 - 03:57 PM

Well, your hash key names are invalid -- they don't come back with a table prefix; you'll need to alias these columns yourself.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 shivabharat

shivabharat
  • Members
  • PipPipPip
  • Advanced Member
  • 371 posts
  • LocationChennai, India

Posted 30 September 2006 - 04:41 PM

Hi,

Please make sure you use the code tags when you post anything. Its becomes very hard for us to go thorugh your posting.
Knowledge --- Reading Enriches Mind But Sharing Enhances It.[br][br]Note: Before you request help enusre that you have had a look at the tutorials @phpfreaks

#4 deedee

deedee
  • New Members
  • Pip
  • Newbie
  • 2 posts

Posted 01 October 2006 - 01:04 AM

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.tropicale...hp?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.



#5 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 01 October 2006 - 02:34 PM

I'm not sure I understand how that table is being populated.
Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users