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 "₱ ".number_format($value, 2);
}
?>
Jun_7 0 Newbie Poster
ryantroop 177 Practically a Master Poster
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 descGood Luck!
Ryan
Edit: You will find much joy in learning about aliases. So much less typing :-/
Jun_7 0 Newbie Poster
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 ryantroop 177 Practically a Master Poster
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?
Jun_7 0 Newbie Poster
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....
Jun_7 0 Newbie Poster
yes limit by the specific date range from the inputed dates.
Jun_7 0 Newbie Poster
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 Jun_7 0 Newbie Poster
i think my tables are so complicated
ryantroop 177 Practically a Master Poster
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?
Jun_7 0 Newbie Poster
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.
ryantroop 177 Practically a Master Poster
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.
Jun_7 0 Newbie Poster
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..
ryantroop 177 Practically a Master Poster
in your example code there.. your room_id for the reservation is NULL... is that right?
ryantroop 177 Practically a Master Poster
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 byroom.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.