Jump to content
Larry Ullman's Book Forums

Using Intermediary Table In Query


Recommended Posts

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

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

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

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

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

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


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

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

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

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

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

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

 Share

×
×
  • Create New...