Jump to content


Photo

Need help joining 3 tables and displaying


  • Please log in to reply
5 replies to this topic

#1 Sephiroth_ronin

Sephiroth_ronin
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 17 August 2006 - 06:19 PM

Hello everyone, I need your help !
This is what I have as my 3 tables and their variables:
TABLE NAME: room_type
- ROOMTYPE_id
- ROOMTYPE_hostel
- ROOMTYPE_beds
- ROOMTYPE_bathroom

TABLE NAME: season
- SEASON_id
- SEASON_name
- SEASON_start
- SEASON_end

TABLE NAME: prices
- PRICE_id
- SEASON_id
- ROOMTYPE_id
- PRICE

Now I would like to have the results displayed in a PHP table like so:

                            SEASON_name1    SEASON_name2 ...
ROOMTYPE_beds1    PRICE                  PRICE
ROOMTYPE_beds2    PRICE                  PRICE
...

Can someone help me !?

#2 hitman6003

hitman6003
  • Members
  • PipPipPip
  • Advanced Member
  • 1,807 posts

Posted 17 August 2006 - 06:39 PM

Write your query like any other join query:

SELECT table1.field1, table1.field2, table2.field1
FROM table1
  LEFT JOIN table2 ON table1.field = table2.field
WHERE table1.somefield = 'somevalue';

THen loop through the results like normal and create a table.

#3 Sephiroth_ronin

Sephiroth_ronin
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 18 August 2006 - 09:46 AM

Thank you for that ! Can you give me an example of a loop that I could use and that you know of ?

Thanks again for you help !

#4 Cook

Cook
  • Members
  • PipPipPip
  • Advanced Member
  • 64 posts
  • LocationSingapore

Posted 18 August 2006 - 09:51 AM

A simple version:

$query = "the query that hitman6003 gave you";
$result = mysql_query($query);
while ($row = mysql_fetch_row($result))
{
  list($field1, $field2, $field3) = $row;
  // do stuff with data
}
Cook

#5 Sephiroth_ronin

Sephiroth_ronin
  • New Members
  • Pip
  • Newbie
  • 3 posts

Posted 25 August 2006 - 04:48 PM

Ok I got the data from the query. The only problem I have is that I want to show the information in a table with SEASON_name as rows, ROOMTYPE_name as colums and in the cell joining the row and column : the PRICE (if there is one, if not = NULL !?!).

It should look like this :


                            SEASON_name1          SEASON_name2            ...
ROOMTYPE_name1    PRICE                        PRICE
ROOMTYPE_name2    PRICE2                        PRICE
...

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 25 August 2006 - 06:24 PM

To output like that you will need to store the data in an array as you read from the db then output the array contents. Try
<?php
include 'db.php';  // connect to database

$sql = "SELECT r.roomtype_beds, s.season_name, p.price
        FROM room_type r
        INNER JOIN prices p ON r.roomtype_id=p.roomtype_id
        INNER JOIN season s ON p.season_id = s.season_id
        ORDER BY r.roomtype_beds, s.season_name";
$res=mysql_query($sql) or die(mysql_error());

$prices = $seasons = array();
while (list($beds, $season, $price)=mysql_fetch_row($res)) {
    $prices[$beds][$season] = $price;
    //
    //   get season names for headings
    //
    if (!in_array($season, $seasons)) {
       $seasons[] = $season;
    }
}
echo "<table border='1' cellpadding='2' cellspacing='0'>";
    // headings
echo '<TR><TH>Beds</TH>';
foreach ($seasons as $s) {
    echo "<TH>$s</TH>";
}
echo '<TR>';
    // prices
foreach ($prices as $beds => $sdata) {
    echo "<TR><TH>$beds</TH>";
    foreach ($sdata as $price) {
        echo "<TD>$price</TD>";
    }
    echo '<TR>';
}
echo '</table>';

?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users