HartleySan Posted March 5, 2014 Share Posted March 5, 2014 Wagtail, I want to help you, but I'm really having a lot of trouble trying to understand what you want. Could you please explain clearly (with actual code and/or an exact list of) what you want? Thank you. Link to comment Share on other sites More sharing options...
Wagtail Posted March 5, 2014 Author Share Posted March 5, 2014 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 More sharing options...
HartleySan Posted March 5, 2014 Share Posted March 5, 2014 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 More sharing options...
Wagtail Posted March 6, 2014 Author Share Posted March 6, 2014 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 More sharing options...
HartleySan Posted March 6, 2014 Share Posted March 6, 2014 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 More sharing options...
Wagtail Posted March 7, 2014 Author Share Posted March 7, 2014 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 More sharing options...
HartleySan Posted March 7, 2014 Share Posted March 7, 2014 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. 1 Link to comment Share on other sites More sharing options...
Wagtail Posted March 7, 2014 Author Share Posted March 7, 2014 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 More sharing options...
HartleySan Posted March 7, 2014 Share Posted March 7, 2014 Use print_r to print out the rows returned from the DB, and I think you can figure it out. 1 Link to comment Share on other sites More sharing options...
Wagtail Posted March 8, 2014 Author Share Posted March 8, 2014 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 More sharing options...
HartleySan Posted March 8, 2014 Share Posted March 8, 2014 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 More sharing options...
Wagtail Posted March 8, 2014 Author Share Posted March 8, 2014 Hi HartleySan, it's working! Some things I can figure out but this was beyond my capabilities. Thank you very much for sticking around and helping me solve this problem. I really appreciate it! Case closed!!! Link to comment Share on other sites More sharing options...
Wagtail Posted March 28, 2014 Author Share Posted March 28, 2014 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 More sharing options...
Antonio Conte Posted March 28, 2014 Share Posted March 28, 2014 Use ORDER BY to define the order. It's that simple. Link to comment Share on other sites More sharing options...
Wagtail Posted March 28, 2014 Author Share Posted March 28, 2014 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 More sharing options...
Antonio Conte Posted March 28, 2014 Share Posted March 28, 2014 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? 1 Link to comment Share on other sites More sharing options...
Wagtail Posted March 28, 2014 Author Share Posted March 28, 2014 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 More sharing options...
HartleySan Posted March 28, 2014 Share Posted March 28, 2014 Yes, congrats, Antonio! You've earned it. Good luck with your first web dev job. Link to comment Share on other sites More sharing options...
Larry Posted March 28, 2014 Share Posted March 28, 2014 Congrats, Antonio! That's fantastic. Very happy to hear it! Link to comment Share on other sites More sharing options...
Antonio Conte Posted March 29, 2014 Share Posted March 29, 2014 Thanks, guys. I'll still be hanging around. Really like these forums. Link to comment Share on other sites More sharing options...
Larry Posted March 30, 2014 Share Posted March 30, 2014 I really like having you in these forums! Please post (or send me via email) updates. I love to hear people's experiences doing the actual job that they may have partially learned how to do using my books. Link to comment Share on other sites More sharing options...
Antonio Conte Posted March 31, 2014 Share Posted March 31, 2014 My new boss was already familier with you as a writer and have read several of your books. He where seriously imperessed I was asked to give you feedback on your work, no matter how small the task was. I'll send you personal updates when appropriate. Link to comment Share on other sites More sharing options...
Larry Posted April 1, 2014 Share Posted April 1, 2014 Cool! Thanks for sharing that extra tidbit. And you should tell your boss that I've very much appreciated your input (and help in these forums) over the years! I'm sure he's getting a great coworker in you. Link to comment Share on other sites More sharing options...
Recommended Posts