Jump to content

Archived

This topic is now archived and is closed to further replies.

Sephiroth_ronin

Need help joining 3 tables and displaying

Recommended Posts

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 !?

Share this post


Link to post
Share on other sites
Write your query like any other join query:

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

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

Share this post


Link to post
Share on other sites
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
}

Share this post


Link to post
Share on other sites
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
...

Share this post


Link to post
Share on other sites
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
[code]
<?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>';

?>
[/code]

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.