Wagtail

Hey HartleySan, thank you for your prompt reply. I'm using the intermediary table which contains the hotel_id and the feaures_id, so each hotel will have multiple features. I tried the OR but it returns hotels that have only one of the 2 example features, but I need the hotels that have both features. Will try to get it right. Thanks. Hope your Monday is off to a good start.

Hello everyone, I'm having a slight difficulty getting a query to work. What I'm trying to do is return hotels that have certain features such as "dry cleaning" etc. This query (pseudo code) doesn't work. It returns a message saying: MySQL returned an empty result set (i.e. zero rows). ( Query took 0.0009 sec ) SELECT ... WHERE... f.features_name='dry cleaning' && f.features_name='spa' This query returns hotels that have "dry cleaning" and/or "spa". What must I do so only hotels are returned that have both features? SELECT...WHERE ...f.features_name IN ('dry cleaning', 'spa') Thank you in advance.

Never mind! It does work after all. Thanks.

The DELETE query doesn't work because I have a second query that INSERTS values into the same table. If I remove the latter query, then the former query will work. In my code I first check if ($_SERVER['REQUEST_METHOD'] == 'POST'), then I have the code for the DELETE query, followed by the code for the INSERT query. Do you think that this should work? I'm trying to figure this out. Thank you.

HartleySan, thank you for the link. The query is working well now - no duplicate values are being inserted into the table. Now I'm trying to delete certain rows but no luck so far. Nothing seems to happen, although the query does work in PHPmyAdmin. Must I first run a SELECT query? Hope you are well. Thank you!

Hi Larry and HartleySan, thank you for your help. I've made the combination of hotel_id and features_id the primary key. No, I am trying to prevent duplicate values being inserted into the table. What I have done is create checkboxes for all of the features. If 'le grand hotel' has 5 features, then those 5 checkboxes will already be checked. If I now check 2 more checkboxes, I'll have 7 checkboxes checked, but only 2 checkbox values must be inserted into the table (the other 5 checkbox values are already present in the intermediary table). What would be nice is if the query could also delete those rows when the associated checkboxes are "unchecked". Does this make sense? Thanks!

Hello everyone, please be so kind and help me with the following query. I have an intermediary Hotels_features table into which I'm inserting some values. How can I prevent the insertion of duplicate values? I can't have the same features_id be associated with the same hotel_id multiple times. I think one can use "on duplicate key update" but I'm not sure how to integrate this with the INSERT INTO query. An example query: INSERT INTO `Hotels_features` (`hotel_id`, `features_id`) VALUES ( 2,7), (2, 2), (2, 4) Thank you very much!

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

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: Thank you.

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!

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 )

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. 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>'; } }

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!

Seems to be "mission impossible" to get the same query to loop out the 3 rooms and the 5 features.

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.

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!

No errors but it returned too many rows - it appears to have looped out the 3 rooms at least 9 times.

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' Sorry about that, I did use a string when running the query from phpMyAdmin. Thanks!

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'

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.

Probably, I just thought they were a bit high compared to the viewings my other posts received - but it doesn't really matter. I'm enjoying your book Larry, thanks.

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!

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!

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?

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.