Wagtail Posted March 2, 2014 Share Posted March 2, 2014 Hi everyone, I'm really having trouble with a query. The query needs to loop out all of the rooms and amenities of a particular hotel. This wasn't a problem until I added an intermediary table. Because the hotel ID appears X amount of times in the intermediary “hotels_features” table, the rooms and amenities will loop out X amount of times, instead of only once. I am not sure if the query is the problem or what. In the code I've posted I only included a while loop for the rooms. Please assist me with this if you can. Thank you very much. $q = "SELECT column names FROM hotels AS h INNER JOIN rooms AS r ON r.hotel_id=h.hotel_id INNER JOIN hotels_features AS hf ON hf.hotel_id = h.hotel_id INNER JOIN features AS f ON f.features_id = hf.features_id ...WHERE category = '$category'"; $r = @mysqli_query($dbc, $q); $row = mysqli_fetch_array($r, MYSQLI_ASSOC); // echo data here $r2 = @mysqli_query ($dbc, $q); while ($row2 = mysqli_fetch_array($r2, MYSQLI_ASSOC)) { // loop content here } // continuing echoing data here Link to comment Share on other sites More sharing options...
HartleySan Posted March 2, 2014 Share Posted March 2, 2014 Could you please provide the structure of your DB with the relevant tables? Thanks. Link to comment Share on other sites More sharing options...
Wagtail Posted March 2, 2014 Author Share Posted March 2, 2014 Hi HartleySan, how are you doing? Sorry to bother you with this - it's the weekend after all. As I mentioned earlier, everything was working fine until I used the columns from the intermediary table in the query. The multiple hotel_ids appear to be the problem. Hotels: hotel_id Rooms: room_id hotel_id other columns such as room name, rate etc Features: features_id features_name Hotels_features: hotel_id features_id Thanks for your help. Link to comment Share on other sites More sharing options...
StephenM Posted March 2, 2014 Share Posted March 2, 2014 Hi, Did you try and run the SQL query on its own in phpmyadmin? It might reveal more information. Link to comment Share on other sites More sharing options...
HartleySan Posted March 2, 2014 Share Posted March 2, 2014 Something like the following should work: SELECT columns-you-want FROM Hotels AS h, Rooms AS r, Features AS f, Hotels_features AS hf WHERE h.hotel_id = r.hotel_id AND h.hotel_id = hf.hotel_id AND f.features_id = hf.features_id AND h.hotel_id = ?; Link to comment Share on other sites More sharing options...
Wagtail Posted March 3, 2014 Author Share Posted March 3, 2014 Thanks for getting back to me HartleySan. I tried that but the query would not work - "mysqli_num_rows() expects parameter 1 to be mysqli_result, boolean given". @StephenM - thanks, all I can see is that the hotel_Ids in the intermediary table are multiplying with the rooms. If the hotel has 3 features and 3 rooms then $num = mysqli_num_rows($r2) wil be 9. The same 3 rooms will be looped out 3 times. The same 3 features will also be looped out 3 times. Perhaps mysqli_fetch_array can be divided by the number of times the hotel_id appears in the intermediary table? Link to comment Share on other sites More sharing options...
HartleySan Posted March 3, 2014 Share Posted March 3, 2014 What's the exact query you ran, as my query did require some modification? Link to comment Share on other sites More sharing options...
Wagtail Posted March 3, 2014 Author Share Posted March 3, 2014 I've used the same format as the code that you provided. For the columns I've used table alias.column name such as SELECT h.hotel_name, r.room_name, f.features_name FROM Hotels AS h, Rooms AS r, Features AS f, Hotels_features AS hf WHERE h.hotel_id = r.hotel_id AND h.hotel_id = hf.hotel_id AND f.features_id = hf.features_id AND h.hotel_name = '$name' AND h.hotel_id = ?; I left out h.hotel_name = '$name' but that also didn't make any difference. Thanks! Link to comment Share on other sites More sharing options...
HartleySan Posted March 3, 2014 Share Posted March 3, 2014 You should not include the h.hotel_id = ? part unless you're using a prepared statement. Try using some real, static values, and execute the queries on the DB from phpMyAdmin, as Stephen suggested. Thanks. Link to comment Share on other sites More sharing options...
Wagtail Posted March 4, 2014 Author Share Posted March 4, 2014 Morning HartleySan, As suggested, I ran a number of queries on the database using phpMyAdmin. I ran the query which I used in my first post, and then used the EXPLAIN command. The EXPLAIN command has shown that my hotels, rooms and Hotels_features tables all have "ref" type values, the other tables have "eq_ref" type values. The "keys" used for the rooms and Hotels_features tables is the hotel_id. The "ref" value for the rooms table is h.hotel_id while the ref value for the Hotels_features table is r.hotel_id. Is this the problem? The "extra" values for both hotels and Hotels_features tables is "using where". All tables have a "key_len" of 4 except the hotels table which has a value of 502. I have re-arranged the order of my columns and the inner joins but the result is still the same. Do you think a straight join might solve the problem? Thank you for your help! Link to comment Share on other sites More sharing options...
HartleySan Posted March 4, 2014 Share Posted March 4, 2014 So the query I provided doesn't work? I'm confused. Link to comment Share on other sites More sharing options...
Wagtail Posted March 4, 2014 Author Share Posted March 4, 2014 I tried the query using the format that you provided, but without the h.hotel_id = ? I'm now having even more rows looped out. Link to comment Share on other sites More sharing options...
HartleySan Posted March 4, 2014 Share Posted March 4, 2014 Please show me the exact query you're executing from phpMyAdmin. Link to comment Share on other sites More sharing options...
Wagtail Posted March 4, 2014 Author Share Posted March 4, 2014 SELECT specific columns in table alias.column format FROM hotels AS h, hotels_features AS hf, features AS f, rooms AS r WHERE h.hotel_id = r.hotel_id AND h.hotel_id = hf.hotel_id AND f.features_id = hf.features_id AND h.hotel_name = '$name' Link to comment Share on other sites More sharing options...
HartleySan Posted March 4, 2014 Share Posted March 4, 2014 What does the following mean? SELECT specific columns in table alias.column format Also, if you are executing the query from phpMyAdmin, you can't use PHP variables. You need to actually enter a string value for $name. That make sense? Link to comment Share on other sites More sharing options...
Wagtail Posted March 4, 2014 Author Share Posted March 4, 2014 Hi HartleySan, the actual query would include column names such as h.hotel_name, r.room_name, f.features_name etc. I select all relevant columns except any ID columns. SELECT h.hotel_name, r.room_name, f.features_name FROM hotels AS h, hotels_features AS hf, features AS f, rooms AS r WHERE h.hotel_id = r.hotel_id AND h.hotel_id = hf.hotel_id AND f.features_id = hf.features_id AND h.hotel_name = 'le grand hotel' Also, if you are executing the query from phpMyAdmin, you can't use PHP variables. You need to actually enter a string value for $name. Sorry about that, I did use a string when running the query from phpMyAdmin. Thanks! Link to comment Share on other sites More sharing options...
HartleySan Posted March 4, 2014 Share Posted March 4, 2014 When you execute that query, do you get an error or just no results? Link to comment Share on other sites More sharing options...
Wagtail Posted March 4, 2014 Author Share Posted March 4, 2014 No errors but it returned too many rows - it appears to have looped out the 3 rooms at least 9 times. Link to comment Share on other sites More sharing options...
StephenM Posted March 4, 2014 Share Posted March 4, 2014 Does this modification make any difference? SELECT DISTINCT (h.hotel_name) , r.room_name, f.features_name Link to comment Share on other sites More sharing options...
HartleySan Posted March 4, 2014 Share Posted March 4, 2014 Only if you have multiple instances of a hotel in the hotels table, which does not make sense to me. Really, I think the query might be returning bum results because Wagtail's DB is not properly structured in the first place. Link to comment Share on other sites More sharing options...
Wagtail Posted March 4, 2014 Author Share Posted March 4, 2014 Does this modification make any difference? SELECT DISTINCT (h.hotel_name) , r.room_name, f.features_name Thank you StephenM, I'll try that a bit later. Here is the structure of the database tables: -- -- Table structure for table `hotels_features` -- CREATE TABLE IF NOT EXISTS `hotels_features` ( `hotel_id` int(11) NOT NULL, `features_id` int(11) NOT NULL, KEY `hotel_id` (`hotel_id`), KEY `features_id` (`features_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Table structure for table `hotels` -- CREATE TABLE IF NOT EXISTS `hotels` ( `hotel_id` int(11) NOT NULL AUTO_INCREMENT, `hotel_name` varchar(200) NOT NULL, `features_id` int(11) NOT NULL, PRIMARY KEY (`hotel_id`), KEY `hotel_name` (`hotel_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=14 ; -- -- Table structure for table `rooms` -- CREATE TABLE IF NOT EXISTS `rooms` ( `rooms_id` int(11) NOT NULL AUTO_INCREMENT, `rooms_name` varchar(200) NOT NULL, `hotel_id` int(11) NOT NULL, PRIMARY KEY (`rooms_id`), KEY `hotel_id` (`hotel_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=7 ; -- -- Table structure for table `features` -- CREATE TABLE IF NOT EXISTS `features` ( `features_id` int(11) NOT NULL AUTO_INCREMENT, `features_name` varchar(150) NOT NULL, PRIMARY KEY (`features_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;== Thank you! Link to comment Share on other sites More sharing options...
HartleySan Posted March 4, 2014 Share Posted March 4, 2014 Why does the hotels table have the features_id column? 1 Link to comment Share on other sites More sharing options...
Wagtail Posted March 5, 2014 Author Share Posted March 5, 2014 Why does the hotels table have the features_id column? Sorry, that shouldn't be - it was getting quite late yesterday. I created a new database with new tables and the result is still the same. From what I can tell the hotel_ids in the hotels_features table are multiplying with the hotel_ids in the rooms table. Here is the new database: -- -- Database: `myhotels` -- -- -------------------------------------------------------- -- -- Table structure for table `features` -- CREATE TABLE IF NOT EXISTS `features` ( `features_id` int(11) NOT NULL AUTO_INCREMENT, `features_name` varchar(200) NOT NULL, PRIMARY KEY (`features_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=17 ; -- -- Dumping data for table `features` -- INSERT INTO `features` (`features_id`, `features_name`) VALUES (1, 'free upgrades'), (2, 'free newspapers'), (3, 'aircon'), (4, 'central heating'), (5, 'own balcony'), (6, 'game drives'), (7, 'daily parties'), (8, 'pool'), (9, 'internet'), (10, 'room service'), (11, 'laundry'), (12, 'hiking trails'), (13, 'cinema'), (14, 'shopping'), (15, 'sauna'), (16, 'beauty salon'); -- -------------------------------------------------------- -- -- Table structure for table `hotels` -- CREATE TABLE IF NOT EXISTS `hotels` ( `hotel_id` int(11) NOT NULL AUTO_INCREMENT, `hotel_name` varchar(200) NOT NULL, PRIMARY KEY (`hotel_id`), KEY `hotel_name` (`hotel_name`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=3 ; -- -- Dumping data for table `hotels` -- INSERT INTO `hotels` (`hotel_id`, `hotel_name`) VALUES (2, 'budget hotel'), (1, 'le grand hotel'); -- -------------------------------------------------------- -- -- Table structure for table `hotels_features` -- CREATE TABLE IF NOT EXISTS `hotels_features` ( `hotel_id` int(11) NOT NULL, `features_id` int(11) NOT NULL, KEY `hotel_id` (`hotel_id`), KEY `features_id` (`features_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- -- Dumping data for table `hotels_features` -- INSERT INTO `hotels_features` (`hotel_id`, `features_id`) VALUES (1, 10), (1, 2), (1, 13), (1, 14), (1, 14), (2, 2), (2, 4), (2, 6), (2, 11); -- -------------------------------------------------------- -- -- Table structure for table `rooms` -- CREATE TABLE IF NOT EXISTS `rooms` ( `rooms_id` int(11) NOT NULL AUTO_INCREMENT, `rooms_name` varchar(200) NOT NULL, `hotel_id` int(11) NOT NULL, PRIMARY KEY (`rooms_id`), KEY `hotel_id` (`hotel_id`) ) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ; -- -- Dumping data for table `rooms` -- INSERT INTO `rooms` (`rooms_id`, `rooms_name`, `hotel_id`) VALUES (1, 'standard room', 1), (2, 'superior room', 1), (3, 'wedding suite', 2), (4, 'tented suite', 1); Thanks again. Link to comment Share on other sites More sharing options...
HartleySan Posted March 5, 2014 Share Posted March 5, 2014 This will get you all the possible permutations of room type and features at "le grand hotel": 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'; Link to comment Share on other sites More sharing options...
Wagtail Posted March 5, 2014 Author Share Posted March 5, 2014 Seems to be "mission impossible" to get the same query to loop out the 3 rooms and the 5 features. Link to comment Share on other sites More sharing options...
Recommended Posts