Jump to content
Larry Ullman's Book Forums

Using Intermediary Table In Query


Recommended Posts

Hi HartleySan,

 

I included some of the below code in my first post.

 

I'd like to echo out all the information pertaining to a particular hotel. So I'd echo out an overview of the hotel followed by all of the rooms and all of the features of that particular hotel. Using 'le grand hotel' as an example, a total of 3 rooms should be echoed out, and a total of 5 features. At the moment it's echoing out the 3 rooms 5 times.

$q = "SELECT h.hotel_name, r.rooms_name, f.features_name
FROM hotels AS h, rooms AS r, features AS f, hotels_features AS hf
WHERE h.hotel_id = hf.hotel_id
AND h.hotel_id = r.hotel_id
AND f.features_id = hf.features_id
AND h.hotel_name =  'le grand hotel';";

    $r = @mysqli_query($dbc, $q);   
    $row = mysqli_fetch_array($r, MYSQLI_ASSOC); 


// echo data here such as hotel overview


$r2 = @mysqli_query ($dbc, $q);
while ($row2 = mysqli_fetch_array($r2, MYSQLI_ASSOC)) { 
echo'<p>'.$row2['rooms_name'].'</p>';

// loop out rooms here
// using the myhotels database as an example, for 'le grand hotel', it should loop out 3 rooms here

}

// continue echoing out other hotel information

 $r3 = @mysqli_query ($dbc, $q);
while ($row3 = mysqli_fetch_array($r3, MYSQLI_ASSOC)) {
echo'<p>'.$row3['features_name'].'</p>';

// loop out features here
// using the myhotels database as an example, for 'le grand hotel', it should loop out 5 features here

}





Thanks!

Link to comment
Share on other sites

Yes, it's echoing that out because you're doing a join across three tables, which creates a Cartesian product of all the possible combinations. In other words, if you have three rooms and five features for a single hotel, you get 3 x 5 (or 15) rows back. That is the expected and intended result of the query.

 

If you don't want that, you either need to change your query or edit the data that is returned on the PHP side. The following (unverified) query might be a better solution:

SELECT r.rooms_name
FROM hotels AS h, rooms AS r
WHERE h.hotel_id = r.hotel_id
AND h.hotel_name = 'le grand hotel'
UNION
SELECT f.features_name
FROM hotels AS h, features AS f, hotels_features AS hf
AND h.hotel_id = hf.hotel_id
AND hf.features_id = f.features_id
AND h.hotel_name = 'le grand hotel';

 

Edit: As a side question, why do you not have a hotels_rooms table?

Link to comment
Share on other sites

Thank you for the query.

 

It seems to loop out the correct number of rooms and features for 'le grand hotel', but I'm not sure if it's being looped out in the correct place. I gave the paragraph tags for the rooms a style="color:tan", and a style="color:purple" for the features paragraph tags. But the features that are looped out have the same color as the room paragraphs. In addition, I'm getting an Undefined index: features_name error.

 

 

Edit: As a side question, why do you not have a hotels_rooms table?

 

 

 

From what you can gather, I haven't really used intermediate tables before. Perhaps a stupid question, but what practical benefits are there if I add a hotels_rooms table? The rooms for the specifc hotels were looping out correctly before I added the hotels_features table.

 

 

Thank you for all of your assistance. I'm sure you must be sick of this thread!

$q = "SELECT r.rooms_name, h.hotel_name
FROM hotels AS h, rooms AS r
WHERE h.hotel_id = r.hotel_id
AND h.hotel_name = 'le grand hotel'
UNION
SELECT f.features_name, h.hotel_name 
FROM hotels AS h, features AS f, hotels_features AS hf
WHERE h.hotel_id = hf.hotel_id
AND hf.features_id = f.features_id
AND h.hotel_name = 'le grand hotel'";


$r = @mysqli_query($dbc, $q);   
$num = mysqli_num_rows($r);
$row = mysqli_fetch_array($r, MYSQLI_ASSOC); 
	
echo'<p style="color:red">'.$row['hotel_name'].'</p>';


$r2 = @mysqli_query ($dbc, $q);
// Count the number of returned rows:
$num = mysqli_num_rows($r2);
if ($num > 0) { // If it ran OK, display the records.
// Fetch and print all the records:
while ($row2 = mysqli_fetch_array($r2, MYSQLI_ASSOC)) {
    
Print '<p style="color:tan">'.$row2['rooms_name'].'</p>
';

}
}
  
    
    
$r3 = @mysqli_query ($dbc, $q); 	
// Count the number of returned rows:
$num = mysqli_num_rows($r3);

if ($num > 0) { // If it ran OK, display the records.
// Fetch and print all the records:
while ($row3 = mysqli_fetch_array($r3, MYSQLI_ASSOC)) {
    
echo'<p style="color:purple">'.$row3['features_name'].'</p>';

}
}

Link to comment
Share on other sites

Very likely, the features info is being stored under the rooms_name index, because of how UNION statements work in MySQL. The easy solve is to add the following to your code right after the start of your while loop:

echo '<pre>';
print_r($row2);
echo '</pre>';

By doing so, you can see all the info fetched from the DB. That will likely solve your problem right away.

Link to comment
Share on other sites

I've added the code and it's looping out arrays. As you said, the features are being stored under the rooms_name index. But how to proceed from here to loop out the rooms and features separately?

 

Thank you.

Array
(
    [rooms_name] => standard room
    [hotel_name] => le grand hotel
)

Array
(
    [rooms_name] => superior room
    [hotel_name] => le grand hotel
)

Array
(
    [rooms_name] => tented suite
    [hotel_name] => le grand hotel
)

Array
(
    [rooms_name] => room service
    [hotel_name] => le grand hotel
)

Array
(
    [rooms_name] => free newspapers
    [hotel_name] => le grand hotel
)

Array
(
    [rooms_name] => cinema
    [hotel_name] => le grand hotel
)

Array
(
    [rooms_name] => hiking trails
    [hotel_name] => le grand hotel
)

Array
(
    [rooms_name] => shopping
    [hotel_name] => le grand hotel
)

Link to comment
Share on other sites

You can return a second column for each SELECT statement that is just a unique literal to separate that data. You then check for that literal on the PHP side, and you're good. For example:

SELECT r.rooms_name, 'room'
FROM hotels AS h, rooms AS r
WHERE h.hotel_id = r.hotel_id
AND h.hotel_name = 'le grand hotel'
UNION
SELECT f.features_name, 'feature'
FROM hotels AS h, features AS f, hotels_features AS hf
WHERE h.hotel_id = hf.hotel_id
AND hf.features_id = f.features_id
AND h.hotel_name = 'le grand hotel';
 
Also, in the future, please try to find the answer on your own before quickly coming back here for the answer.
Thank you.
  • Upvote 1
Link to comment
Share on other sites

 

You then check for that literal on the PHP side, and you're good

 

 

Could you please elaborate on this? I'm not sure what you mean by "check for that literal on the PHP side".

 

Must I leave '.$row2['rooms_name'].' and '.$row3['features_name'].' as is?

 

 

Thanks a lot!

Link to comment
Share on other sites

Use print_r to print out the rows returned from the DB, and I think you can figure it out.

 

 

Thanks, using print_r($row2['rooms_name']) in the while loop prints out the same information as does  Print '<p style="color:tan">'.$row2['rooms_name'].'</p>'; in the while loop. And I'm still receiving  Undefined index: features_name.

 

And I still don't know what this means:

 

 

check for that literal on the PHP side

 

 

Thank you.

Link to comment
Share on other sites

I was really hoping you could figure this out on your own, but here's the whole answer.

First off, use this query (notice the "AS type" I added after the two literal strings returned by the query):

SELECT r.rooms_name, 'room' AS type
FROM hotels AS h, rooms AS r
WHERE h.hotel_id = r.hotel_id
AND h.hotel_name = 'le grand hotel'
UNION
SELECT f.features_name, 'feature' AS type
FROM hotels AS h, features AS f, hotels_features AS hf
WHERE h.hotel_id = hf.hotel_id
AND hf.features_id = f.features_id
AND h.hotel_name = 'le grand hotel';
 
Then, in PHP, you can tell whether the returned data is room data or feature data by the following:
if ($row2['type'] === 'room') {
  // It's a room!
} else if ($row2['type'] === 'feature') {
  // It's a feature!
}
 
That's it! Done.
Link to comment
Share on other sites

  • 3 weeks later...

Hi HartleySan,

 

could you please help me with the following?

 

My query now includes 2 unions so I'll have something like:

SELECT r.rooms_name, 'room' AS type
// blah blah...
AND h.hotel_name = 'le grand hotel'

UNION

SELECT f.features_name, 'feature' AS type
// blah blah...
AND h.hotel_name = 'le grand hotel'

UNION

SELECT s.services_name, 'service' AS type
// blah blah...
AND h.hotel_name = 'le grand hotel';

Do you know how I can show the returned info alphabetically?

 

All rooms, features and services must be in alphabetical order:

Standard Room
Wedding Suite

Cinema
Mountain Bike Trails
Pool

Aromatherapy massages
Room service

Please let me know if this can be done.

 

 

Thank you!

Link to comment
Share on other sites

Hi Antonio,

 

how are you doing?

 

Thanks, I have done that but it doesn't seem to order them alphabetically. I put the ORDER BY at the end of the query. If I add separate ORDER BY statements for each SELECT I get an error message.

SELECT r.rooms_name, 'room' AS type
// blah blah...
AND h.hotel_name = 'le grand hotel'

UNION

SELECT f.features_name, 'feature' AS type
// blah blah...
AND h.hotel_name = 'le grand hotel'

UNION

SELECT s.services_name, 'service' AS type
// blah blah...
AND h.hotel_name = 'le grand hotel' ORDER BY type ASC;
Link to comment
Share on other sites

You should be able to change to joins instead and use ORDER BY then.

 

Everything is very good. I accepted my first full-time job as a Web Developer yesterday. I'll by working with the Symfony2 Stack, EZPublish, some front-end coding and some customer meetings and sales. It's the total dream job for me.

 

How's life treating you? :)

  • Upvote 1
Link to comment
Share on other sites

 

You should be able to change to joins instead and use ORDER BY then.

 

Thanks. What I am now doing is looping all of the content into separate arrays which I can then slice, dice and do as I please. As the saying goes 'if in doubt, use an array'.

 

 

 

Everything is very good. I accepted my first full-time job as a Web Developer yesterday. I'll by working with the Symfony2 Stack, EZPublish, some front-end coding and some customer meetings and sales. It's the total dream job for me.

 

Awesome stuff, congratulations Antonio. :)  That must have made your weekend. So I guess you won't be hanging around too much in the forums? Anyway, congrats again and good luck with your new job! Let us know how it goes.

Link to comment
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.
Note: Your post will require moderator approval before it will be visible.

Guest
Reply to this topic...

×   Pasted as rich text.   Paste as plain text instead

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Loading...
 Share

×
×
  • Create New...