3

I have this cinema project for a course. And I have to visually show the seats to user so they can reserve their preferred seats. I need to get the reserved seats from the database of that particular show but I can't seem to get proper sql query. I'm using phpMyAdmin and mySQL.

I just have shid (primary key of showingTimes table) available and from that shid I need to get all the seats.

It's like seats table has foreign key rid (primary key for reservation table) from reservation table and reservation table has foreign key shid from showingTimes table. I know this will be done by inner join but can't get it right.

I previously tried this SQL but this doesn't seem to work.

SELECT `seats`.*, `reservation`.* 
FROM `seats`
LEFT JOIN `reservation` ON `reservation`.`rid` = `seats`.`rid` 
                       AND `reservation`.`shid` = ?;

Either I should change my database and add shid to seats table, but then there will be redundant data.

My database tables are as following. This is the showingtimes table.

CREATE TABLE `showingtimes` (
   `shid` int(11) NOT NULL,
   `mid` int(11) NOT NULL,
   `start` time NOT NULL,
   `end` time NOT NULL,
   `hid` int(11) NOT NULL,
   ADD PRIMARY KEY (`shid`),
   ADD KEY `hid` (`hid`),
   ADD KEY `mid` (`mid`) USING BTREE;
   ADD CONSTRAINT `showingtimes_ibfk_2` FOREIGN KEY (`hid`) REFERENCES 
   `hall` (`hid`),
   ADD CONSTRAINT `showingtimes_ibfk_3` FOREIGN KEY (`mid`) REFERENCES 
   `movies` (`mid`);
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here's some data from this table.

INSERT INTO `showingtimes` (`shid`, `mid`, `start`, `end`, `hid`) VALUES
(1, 109, '03:00:00', '06:00:00', 2),
(2, 112, '03:00:00', '06:00:00', 4);

This is reservation table.

CREATE TABLE `reservation` (
  `rid` int(11) NOT NULL,
  `uid` int(11) NOT NULL,
  `shid` int(11) NOT NULL,
  `nos` int(11) NOT NULL,
  `pay` varchar(10) NOT NULL,
  `day` datetime NOT NULL'
  ADD PRIMARY KEY (`rid`),
  ADD KEY `uid` (`uid`),
  ADD KEY `shid` (`shid`),
  ADD CONSTRAINT `reservation_ibfk_1` FOREIGN KEY (`uid`) REFERENCES 
  `users` (`uid`),
  ADD CONSTRAINT `reservation_ibfk_2` FOREIGN KEY (`shid`) REFERENCES 
  `showingtimes` (`shid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here's some data from this table.

INSERT INTO `reservation` (`rid`, `uid`, `shid`, `nos`, `pay`, `day`) 
VALUES
(1, 4, 2, 3, 'Yes', '2018-12-14 01:00:00'),
(2, 2, 2, 3, 'Yes', '2018-12-14 02:00:00'),
(3, 5, 1, 2, 'Yes', '2018-12-14 01:00:00'),
(4, 4, 1, 2, 'Yes', '2018-12-14 11:00:00');

And this is the seats table.

CREATE TABLE `seats` (
  `sid` int(11) NOT NULL,
  `rid` int(11) NOT NULL,
  `row` varchar(11) NOT NULL,
  `col` int(11) NOT NULL,
  ADD PRIMARY KEY (`sid`),
  ADD KEY `rid` (`rid`),
  ADD CONSTRAINT `seats_ibfk_1` FOREIGN KEY (`rid`) REFERENCES `reservation` 
  (`rid`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And here's some data from this table.

INSERT INTO `seatsrffm` (`sid`, `rid`, `row`, `col`) VALUES
  (1, 1, '1#A', 1),
  (2, 1, '1#A', 2),
  (3, 1, '1#A', 3),
  (4, 2, '2#B', 3),
  (5, 2, '2#B', 4),
  (6, 2, '2#B', 5);

Any help would be greatly appreciated.

  • Your SQL query has been executed successfully. SHOW CREATE TABLE seatsrffm seatsrffm CREATE TABLE seatsrffm ( sid int(11) NOT NUL... Running this query just shows this output – Ramsha Khalid Dec 21 '18 at 11:08
  • 1
    Let us continue this discussion in chat. – Ramsha Khalid Dec 21 '18 at 11:53

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.