In this query i want to display the unbooked rooms, but the booked rooms are displayed instead of the unbooked rooms...

 create table guest (
 guest_id           int auto_increment primary key unique not null,
 guest_lname        varchar(50) not null,
 guest_fname        varchar(50) not null,
 guest_email        varchar(100) not null,
 guest_password     varchar(50) not null,
 guest_address      varchar(20) not null,
 guest_op_adres     varchar(200),
 guest_gender       int(1) not null,
 guest_mobile_no    varchar(15) not null
);

 create table room_rate_default (
defroom_rateID      int auto_increment not null primary key,
room_typeID int not null,
defseason_type  varchar(50) not null,
defmonRate      double not null,
deftuesRate double not null,
defwedRate      double not null,
defthuRate      double not null,
deffriRate      double not null,
defsatRate      double not null,
defsunRate      double not null,
foreign key(room_typeID)references room_type(room_typeID)
);
create table room_rate (
room_rateID     int auto_increment not null primary key,
room_typeID     int not null,
season_type     varchar(50) not null,
monRate         double not null,
tuesRate       double not null,
wedRate     double not null,
thuRate     double not null,
friRate     double not null,
satRate     double not null,
sunRate     double not null,
adult       int,
children    int,
foreign key(room_typeID)references room_type(room_typeID)
);
create table room_type (
    room_typeID    int auto_increment not null primary key,
room_type   varchar(50) not null,
room_type_des  varchar(200)
);
create table room (
  room_id          int unique primary key auto_increment not null,
  room_number      int unique not null,
  room_typeID      int not null,
  room_adultcapacity    int not null,
  room_childcapacity    int not null,
  room_img         varchar(100),
  room_status      int(1),
  foreign key(room_typeID)references room_type(room_typeID)
   );
create table reservation ( 
 reserve_code          varchar(20) primary key unique not null,       
 guest_id              int not null,
 foreign key(guest_id)references guest(guest_id),
 cottage_id       int,
 foreign key(cottage_id)references cottage(cottage_id),
 room_id          int,
 foreign key(room_id)references room(room_id),
 services_id      int,
 foreign key(services_id)references services(services_id),
 reserve_date_start         date,
 reserve_date_end          date,
 reserve_status      int(1)
); 

<?php
$_SESSION["check_in"] = 2018-6-15;
$_SESSION["check_out"] = 2018-6-25;
 $sql = "select distinct room_type.room_type, room_type.room_typeID, room_type.room_type_des, room.room_id, room.room_number, room.room_typeID, room.room_adultcapacity,
         room.room_childcapacity, room.room_img, room.room_status, reservation.room_id, reservation.reserve_date_start, reservation.reserve_date_end
         from room 
         left outer join room_type on room.room_typeID = room_type.room_typeID 
         left outer join room_rate_default on room_type.room_typeID = room_rate_default.room_typeID
         left outer join room_rate on room_type.room_typeID = room_rate.room_typeID
         left outer join reservation on room.room_id = reservation.room_id
         where reservation.reserve_date_start and reservation.reserve_date_end not between  ".strtotime($_SESSION["check_in"])." and ".strtotime($_SESSION["check_out"])." or room.room_id not in(reservation.room_id) order by room.room_id desc";
$res = $con->query($sql);
if($res->num_rows > 0) {
     echo "<table>";
     echo "<tr>";
     echo "<th>Image</th>
           <th>Room No.</th>
           <th>Room Type</th>
           <th>Season</th>
           <th>Monday Rates</th>
           <th>Tuesday Rates</th>
           <th>Wednesday Rates</th>
           <th>Thursday Rates</th>
           <th>Friday Rates</th>
           <th>Saturday Rates</th>
           <th>Sunday Rates</th>
           <th>Action</th>";
     echo "</tr>";
     while($row = $res->fetch_array()) {
         echo "<tr>";
             $explode = explode("/", $row["room_img"]);
             $explode = "../../../../admin/php/page/sub-page/image-upload/".$explode[2];
         echo "<td><img class=\"img-class\" src=".$explode." width=\"100px\" height=\"80px\" style=\"border-radius:5px\" /></td>
              <td style=\"opacity:0.76\">".$row["room_number"]."</td>
              <td style=\"opacity:0.76\">".$row["room_type"]."</td>
              <td style=\"opacity:0.76\">";
             $day = 16;
             $month = 12;
             $day1 = 30;
             $month1 = 6;
             $days = 1;
             $months = 7;
             $days1 = 15;
             $months1 = 12;
             $firsts = strtotime($months."/".$days);
             $seconds = strtotime($months1."/".$days1);
             $first = strtotime($month."/".$day);
             $second = strtotime($month1."/".$day1);
             $curdate = strtotime(date("m",strtotime($_POST["month_from"]))."/".$_POST["day_from"]);
             $curdates = strtotime(date("m",strtotime($_POST["month_to"]))."/".$_POST["day_to"]);

              if($first >= strtotime($curdate) && $second >= strtotime($curdates)) {
              $_SESSION["season"] = "Peak Season";
              $sel = "select * from room_rate_default where room_typeID = ".$row["room_typeID"]." && defseason_type = 'Peak Season'"; 
              $ress = $con->query($sel);
              if($ress->num_rows > 0) {
                while($rows = $ress->fetch_array()) {
                  echo $rows["defseason_type"];

         echo "</td>
              <td style=\"opacity:0.76\">".asPhp($rows["defmonRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["deftuesRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["defwedRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["defthuRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["deffriRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["defsatRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["defsunRate"])."</td>";
              }
             } 
              }
               else if($firsts >= strtotime($curdate) && $seconds >= strtotime($curdates)) {
                 $_SESSION["season"] = "Low Season";
                 $sel = "select * from room_rate_default where room_typeID = ".$row["room_typeID"]." && defseason_type = 'Low Season'"; 
                  $ress = $con->query($sel);
                 if($ress->num_rows > 0) {
                while($rows = $ress->fetch_array()) {
                  echo $rows["defseason_type"];

         echo "</td>
              <td style=\"opacity:0.76\">".asPhp($rows["defmonRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["deftuesRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["defwedRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["defthuRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["deffriRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["defsatRate"])."</td>
              <td style=\"opacity:0.76\">".asPhp($rows["defsunRate"])."</td>";
              }
             } 
             }
             echo "<td><button class=\"add-to-booking-list-button\" type=\"button\" onclick=\"addBooking(".$row["room_id"].")\">Add to booking list</button></td>";
            echo "  </tr>";
     }
     echo "</table>";
}
?>
  </div>
?>
 </div>
  <div class="div-content" id="div-content-4">
  <?php include("my-booking-list.php"); ?>
  </div>
 </div>
</body>
</html>
<?php
function asPhp($value) {
 return "&#8369; ".number_format($value, 2); 
}
?>

So.. I didn't pop this in a fiddle or anything due to laziness... however, I believe I found your problem:

so, you're pulling ALL ROOMS (and appropriate meta data) where the room ID is not in a single instance of reservation.room_id (in most cases, this will be NULL and will never match anyway because in the SQL world NULL != NULL, so you have to match on IS NULL).

If you want to do the "not in" method, it should be
where room.room_id not in (select * from reservation where reservation.room_id = room.room_id) --of course, you need date ranges

however, this is crazy style inefficient.
That basically makes every record in the result set run against a new sub query.

It may be better serving to do:
where reservation.room_id IS NULL --again.. dates

of course, this is only part of the way to your solution. You also need to make sure your date ranges for that reserved room are checked, and that the record is NULL for those date ranges.

I believe this should work for you...

select distinct 
    room_type.room_type, 
    room_type.room_typeID, 
    room_type.room_type_des, 
    room.room_id, 
    room.room_number, 
    room.room_typeID, 
    room.room_adultcapacity,
    room.room_childcapacity, 
    room.room_img, 
    room.room_status, 
    reservation.room_id, 
    reservation.reserve_date_start, 
    reservation.
    reserve_date_end
         from room 
         left outer join room_type on room.room_typeID = room_type.room_typeID 
         left outer join room_rate_default on room_type.room_typeID = room_rate_default.room_typeID
         left outer join room_rate on room_type.room_typeID = room_rate.room_typeID
         left outer join reservation on room.room_id = reservation.room_id
         where 
             reservation.room_id IS NULL
         order by 
             room.room_id desc

Good Luck!

Ryan

Edit: You will find much joy in learning about aliases. So much less typing :-/

    no still not working..
    if i used "is null" or "is null" then select query the room_id become null. 
    All i wanted to do is to pull all the unbooked rooms from room table and the booked rooms from reservation table where room_id is equal room_id from room table where the inputed check in dates and check out dates is not in between from the check in dates and check out dates in the reservation table.

    select distinct room_type.room_type, room_type.room_typeID, room_type.room_type_des, room.room_id, room.room_number, room.room_typeID, room.room_adultcapacity, room.room_childcapacity, room.room_img, room.room_status,
         reservation.reserve_date_start,  reservation.reserve_date_end,  reservation.room_id
         from room left outer  join room_type on room.room_typeID = room_type.room_typeID left outer join room_rate_default on room_type.room_typeID = room_rate_default.room_typeID
         left outer join room_rate on room_type.room_typeID = room_rate.room_typeID
         left outer join reservation on room.room_id = reservation.room_id
         where 
         reservation.reserve_date_start and  reservation.reserve_date_end not between ".$_SESSION["check_in"]." and ".$_SESSION["check_out"]."
         or room.room_id not in ('select * from reservation where reservation.room_id = room.room_id')
         order by 
         room.room_id desc

So.. let me make sure I have your request right:

You want all rooms
-- show the reservation information when a room is reserved
-- limit by a specific date range

is this correct?

i want to display all the available rooms from the inputed dates
i retreive first in the room table and match the room_id from the reservation table if there is a match if there is a match do the not between sql query and display room details if the guest inputed dates are not between the check in and check out dates, but if there is no room_id match from the two tables it will still display the rooms from room table.
but the problem is, if i used the is null query my room_id is become null, and if i used not between query the return data is the booked rooms not the available, and if there is no booked rooms, rows count is zero or there is no display....

yes limit by the specific date range from the inputed dates.

if i used this query available and the booked rooms are displayed and the room_id is become null

select distinct room_type.room_type, room_type.room_typeID, room_type.room_type_des, room.room_id, room.room_number, room.room_typeID, room.room_adultcapacity, room.room_childcapacity, room.room_img, room.room_status,
         reservation.reserve_date_start,  reservation.reserve_date_end,  reservation.room_id
         from room left outer  join room_type on room.room_typeID = room_type.room_typeID left outer join room_rate_default on room_type.room_typeID = room_rate_default.room_typeID
         left outer join room_rate on room_type.room_typeID = room_rate.room_typeID
         left outer join reservation on room.room_id = reservation.room_id
         where ".$_SESSION["check_in"]." >= reservation.reserve_date_start and reservation.reserve_date_end >= ".$_SESSION["check_in"]." or reservation.room_id is null
         order by 
         room.room_id desc

i think my tables are so complicated

Your tables are not complicated.

However, I am still struggling to have a plain English explanation of what you want (and is likely the root of your problem. If you can't explain it, you likely can't express it in code).

Can you explain in a simple way what you want to see?

When you say "available" rooms, it doesn't seem to make sense that you also want rooms that have a reservation.

Maybe.. can you give me an example of the output you expect to see?

yes i want all the available rooms including the reserved rooms where the inputed check in and check out dates of the present guest is not in between of the reserved rooms check in and check out dates.

where the inputed check in and check out dates of the present guest...

things like this make it confusing. Another attempt at clarification please:

Given a date range (fromDate and toDate):
Am I correct that you want:

  • All rooms, regardless if reserved or not
  • All rooms should show their reserved state if they are reserved.

To me, this means you're going to have a very large result set. You will get a list of all rooms for every day in the date range. Otherwise you will have false positives for certain dates. If that's what you want, that's fine. It's just a lot of potential rows to deal with (but it is not an abnormal request). To clarify: if you have 100 rooms in your hotel, and a date range of 3 days, then you will have 300 results.

If instead you are looking for a specific date:

Given a date (singleDate):

  • All rooms, regardless if reserved or not.
  • Show state of room (reserved or not) for that specific date.

This is far more straight forward, but it makes looking up date ranges very difficult (as you have to run the query for each date in the range, and again you get a ton of rooms to parse through for each date. But that's ok if that's what you need).

So now this comes down to the "what are you trying to do" question... Make your data request match what you are doing.

INSERT INTO `reservation` (`reserve_code`, `guest_id`, `room_id`, `reserve_date_start`, `reserve_date_end`, `reserve_status`) VALUES
('p28ypfgWO3Ijfn3eO5Vk', 18, NULL, 2, '2018-06-18', '2018-06-22', 1),
('67Bs8MKTOjZ2eJ9fxMPF', 18, NULL, 3, '2018-06-24', '2018-06-29', 1);

INSERT INTO `room` (`room_id`, `room_number`, `room_typeID`, `room_adultcapacity`, `room_childcapacity`, `room_img`, `room_status`) VALUES
(2, 102, 2, 2, 2, '../image-upload/room7.jpg', 1),
  (3, 103, 2, 2, 2, '../image-upload/room8.jpg', 1),
(4, 104, 3, 2, 2, '../image-upload/room9.jpg', NULL),
(5, 105, 3, 2, 1, '../image-upload/room10.jpg', NULL);

desire-check-in-dates date("2018-06-17")
desire-check-out-dates date("2018-06-23")

"select distinct room_type.room_type, room_type.room_typeID, room_type.room_type_des, room.room_id, room.room_number, room.room_typeID, room.room_adultcapacity, room.room_childcapacity, room.room_img, room.room_status
                 from room left outer  join room_type on room.room_typeID = room_type.room_typeID left outer join room_rate_default on room_type.room_typeID = room_rate_default.room_typeID
                 left outer join room_rate on room_type.room_typeID = room_rate.room_typeID
                 where room.room_id not in ('select room_id from reservation where date('2018-06-17') between 
           reserve_date_start and reserve_date_end or date('2018-06-23') between 
           reserve_date_start and reserve_date_end or date('2018-06-17') < 
           reserve_date_start and date('2018-06-23') > reserve_date_end') or room.room_status is null";

in this query i want to display the room_id 3, 4 and 5 but my query is not working..

in your example code there.. your room_id for the reservation is NULL... is that right?

http://www.sqlfiddle.com/#!9/c6632/15

I believe this will work for you (I gave you two versions to work with)

 select
  room_type.room_type, 
  room_type.room_typeID, 
  room_type.room_type_des, 
  room.room_id, 
  room.room_number, 
  room.room_typeID, 
  room.room_adultcapacity, 
  room.room_childcapacity, 
  room.room_img, 
  room.room_status
from 
  room 
    left outer join room_type on room.room_typeID = room_type.room_typeID 
    left outer join room_rate_default on room_type.room_typeID = room_rate_default.room_typeID
    left outer join room_rate on room_type.room_typeID = room_rate.room_typeID
 where
  room.room_id NOT IN (select room_id from reservation where reservation.room_id = room.room_id and 
          (reservation.reserve_date_start between cast("2018-06-17" as date) and cast("2018-06-23" as date)));

 select
  room_type.room_type, 
  room_type.room_typeID, 
  room_type.room_type_des, 
  room.room_id, 
  room.room_number, 
  room.room_typeID, 
  room.room_adultcapacity, 
  room.room_childcapacity, 
  room.room_img, 
  room.room_status
from 
  room 
    left outer join room_type on room.room_typeID = room_type.room_typeID 
    left outer join room_rate_default on room_type.room_typeID = room_rate_default.room_typeID
    left outer join room_rate on room_type.room_typeID = room_rate.room_typeID
    left outer join reservation on reservation.room_id = room.room_id and reservation.reserve_date_start between cast("2018-06-17" as date) and cast("2018-06-23" as date)
where
  reservation.room_id IS NULL
group by

room.room_id;

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.