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
Recommended Answers
Jump to PostSo.. 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 …
Jump to PostSo.. 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 rangeis this correct?
Jump to PostYour 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 …
Jump to Postwhere 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 …
All 13 Replies
ryantroop
177
Practically a Master Poster
Jun_7
0
Newbie Poster
ryantroop
177
Practically a Master Poster
Jun_7
0
Newbie Poster
Jun_7
0
Newbie Poster
Jun_7
0
Newbie Poster
Jun_7
0
Newbie Poster
ryantroop
177
Practically a Master Poster
Jun_7
0
Newbie Poster
ryantroop
177
Practically a Master Poster
Jun_7
0
Newbie Poster
ryantroop
177
Practically a Master Poster
ryantroop
177
Practically a Master Poster
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.