Welcome to the Treehouse Community

Want to collaborate on code errors? Have bugs you need feedback on? Looking for an extra set of eyes on your latest project? Get support with fellow developers, designers, and programmers of all backgrounds and skill levels here with the Treehouse Community! While you're at it, check out some resources Treehouse students have shared here.

Looking to learn something new?

Treehouse offers a seven day free trial for new students. Get access to thousands of hours of content and join thousands of Treehouse students and alumni in the community today.

Start your free trial

PHP

Posted by yannverreault
yannverreault
12,599 Points

mysql data for a specific item is showing to every item but shouldn't.

Hello,

I have a database with two tables. One table with hotel information and one table with room information. I join these two tables together with "hotel_id" in my rooms table.

When I select an hotel on my index.php page, it brings me to the hotel's page and it display the hotel's informations and rooms informations for that specific hotel.

Unfortunately, I found out that the same room information is displayed to every hotels no matter what I'm doing. The hotel info is right but not the room info.

I am really noob at this and help would be appreciated.

<?php
require_once("inc/database.php");
require_once("inc/functions.php"); 
require_once("inc/header.php");

#
if(!empty($_GET['id'])){
  $id = intval($_GET['id']);
}

try {
  $results = $db->prepare('SELECT * 
                           FROM hotels 
                           WHERE id = ?');
  $results->bindParam(1, $id);
  $results->execute();
} catch(Exception $e) {
    echo $e->getMessage();
    die();
}

$hotel = $results->fetch(PDO::FETCH_ASSOC);

if($hotel == FALSE) {
  echo 'Sorry the hotel could not be found with the provided ID.';
};

try {
  $roominfo = $db->prepare('SELECT hotels.name, rooms.* 
                            FROM hotels 
                            INNER JOIN rooms 
                            ON rooms.hotel_id = hotels.id');
  $roominfo->execute();
} catch(Exception $e) {
    echo $e->getMessage();
    die();
  };
?>

code below to display room info

<?php
                  foreach($roominfo as $room){
                    echo '<div class="room group">
                            <a href="../Rooms/Aria-DeluxeRoom.html"><h3 class="roomname">'.$room['name'].'</h3></a> 
                            <a href="../Rooms/Aria-DeluxeRoom.html"><img class="roomimg" src='.$room['image'].'></a> 
                            <div class="description">
                              <p>'.$room['description'].'</p>
                            </div>
                          </div>';
                  }

          ?>

2 Answers

jcorum
jcorum
71,830 Points

Since you have this code at the top of the page I assumed you had an id=value in the query string, $id would get initialized here:

if(!empty($_GET['id'])){
  $id = intval($_GET['id']);
}

and String interpolation would work. Since it doesn't appear to, try this instead:

$roominfo = $db->prepare('SELECT hotels.name, rooms.* 
FROM hotels 
INNER JOIN rooms ON rooms.hotel_id = hotels.id 
WHERE rooms.hotel_id = ' . $id);

Here the $id variable is being concatenated with the SELECT string, rather than interpolated in it.

yannverreault
yannverreault
12,599 Points

Thank you jcorum, it's working and it's really appreciated. Is there a paypal adress so I can buy you a beer? ;)

jcorum
jcorum
71,830 Points
jcorum
jcorum
71,830 Points

This SQL joins the two tables but it doesn't filter for any particular hotel:

SELECT hotels.name, rooms.* 
                            FROM hotels 
                            INNER JOIN rooms 
                            ON rooms.hotel_id = hotels.id

I would have expected you to have this as well:

                            WHERE rooms.hotel_id = $id;

In short, joining isn't enough. You have to limit the results of the select to the selected hotel.

yannverreault
yannverreault
12,599 Points

It actually doesn't work because now I have this error message.

SQLSTATE[42S22]: Column not found: 1054 Unknown column '$id' in 'where clause'