使用2个表格获得结果

Question

I am making a reservation system for a school project but now I got stuck.

I have a page where you can check if rooms are available for a specific room category and date

I know that you have to do a inner join. I have used google but I don't have a clue how to do it.

This is in my hotel database:

I have a rooms table:

TABLE `rooms` (
  `roomNR` int(11) NOT NULL,
  `catagory` varchar(11) DEFAULT NULL,
  `picLocation` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`roomNR`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

And I have a reservation table where all the reservations are stored.

TABLE `reservation` (
  `reservationID` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `userID` int(11) NOT NULL,
  `roomNR` int(11) NOT NULL,
  `start` date NOT NULL,
  `end` date NOT NULL,
  `price` float NOT NULL,
  PRIMARY KEY (`reservationID`),
  UNIQUE KEY `userID` (`userID`),
  UNIQUE KEY `roomNR` (`roomNR`),
  CONSTRAINT `reservation_ibfk_1` FOREIGN KEY (`roomNR`) REFERENCES `rooms` (`roomNR`),
  CONSTRAINT `reservation_ibfk_2` FOREIGN KEY (`userID`) REFERENCES `users` (`userID`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=latin1;

This is the form that I use:

<form action="" method="GET">
<div>
    <label for="StartDate">From</label>
    <input type="input" name="startDate" id="startDate" readonly=""> <label for="EndDate">Till</label> <input readonly="" type="input" name="endDate" id="endDate">
        <label for="Catagory">Catagory:</label>
        <select name="Catagory" id="Catagory">
            <?php 

            $catagorys = DB::Getinstance()->query('SELECT * FROM catagory');

            if ($catagorys->count()) {
                foreach($catagorys->results() as $catagory){?>
                <option value="<?php echo $catagory->name; ?>"><?php echo $catagory->name; ?></option>

                <?php }
            }

            ?>
        </select> <input type="submit" value="get available rooms" class="btn btn-default">
        </div>



    </form>

I hope some one can help me!

thank you in advance!

-- edit

Now that I'm looking at the reservation table wouldn't it be better to store also the catagory in there?

Answer 1

Part 1: Rooms that are not reserved

It is a good practice to first streamline the task into simple words:

Select those rooms, which are not referred to in any reservation entry, and are from a given category.

What you in fact need here is not an INNER JOIN. At least not for this part of the task. INNER JOIN will be used to "link" with catagory later.

Using INNER JOIN here could easily select rooms that are already reserved: SELECT rooms.roomNR FROM rooms INNER JOIN reservation ON rooms.roomNR=reservation.roomNR WHERE catagory=$category_id. Of course you could process this information in PHP and use that to select the remaining rows in your table pretty efficiently, but there is a much better way.

You need a subquery.

SELECT roomNR FROM `rooms` WHERE catagory=$category_id AND roomNR NOT IN (SELECT roomNR FROM `reservation`)

It is a fortunate feature of MySQL (or SQL, in general), that the language syntax tries to resemble english written language as much as possible. Query explanation: we SELECT the roomNR of those rooms that are not in the SELECT roomNR FROM reservation result (or, simply put, the reservation table).

For syntax and more information regarding subqueries, please check out http://dev.mysql.com/doc/refman/5.1/en/subqueries.html.

Part 2: PHP Implementation

To take this one step further, and follow your business logic, the following can be said:

Select those categories, that have rooms (at least one room) not referenced in reservation

And this is where you can implement an INNER JOIN. Translating our problem to MySQL - assuming you have id and name fields for catagory - we have:

SELECT catagory.name FROM catagory INNER JOIN rooms ON catagory.id=rooms.catagory WHERE rooms.roomNR NOT IN (SELECT roomNR FROM reservation);

If I'm not mistaken, this query should query a given catagory name if there is at least one room with that catagory id not referenced in reservations. Please check back if this works for you, I'm not in an environment where I could test this right now.

EDIT: DISTINCT keyword

If the query, by any means, returns a given catagory multiple times, please use the DISTINCT keyword:

SELECT DISTINCT catagory.name FROM catagory ...

Part 3: Filter results by start and end date

To filter by start and end date, we come to the following problem:

Select those categories, that have at least one room not referenced in reservations. Reservations that are outside our time scope (example: reservation has already expired by the time the next user begins their own reservation) can be ignored, since their rooms are free, so they no longer limit our possibilities.

Variables used in the next query are these:

$start_date = $_POST['startDate'];
$end_date = $_POST['endDate'];

To make it into SQL:

SELECT catagory.name FROM catagory INNER JOIN rooms ON catagory.id=rooms.catagory WHERE rooms.roomNR NOT IN (SELECT roomNR FROM reservation WHERE end<$start_date OR start>$end_date);

As you can see, we use an inverse logic in the subquery. When we are checking if a given room is already reserved (already in reservation), we ignore those reservations that do not fall into time criteria: it has ended before next reservation begins, or starts before the next reservation ends. Therefore it won't be a limiting element for the NOT IN clause.