I'm trying to build a booking system for hotels, and i can now insert a new booking, but i want it to check if the room is free on the dates of the booking!
Database Schema
CREATE TABLE IF NOT EXISTS `bookings` (
`booking_id` int(10) NOT NULL AUTO_INCREMENT,
`user_id` int(10) NOT NULL,
`room_no` int(10) NOT NULL,
`hotel_id` int(10) NOT NULL,
`from_time` date DEFAULT NULL,
`to_time` date DEFAULT NULL,
PRIMARY KEY (`booking_id`),
KEY `FK_Users_id` (`user_id`),
KEY `user_id` (`user_id`),
KEY `user_id_2` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin AUTO_INCREMENT=15 ;
Php
include_once 'DbConnection.php';
$room_no = $_POST['roomNo'];
$hotel_id = $_POST['hotel_id'];
$from_date = $_POST['from'];
$to_date = $_POST['to'];
$user_id = $_SESSION['id'];
$date_checker_sql ="SELECT * FROM bookings WHERE hotel_id = $hotel_id";
$result = $mysqli->query($date_checker_sql);
while($row = $result->fetch_object()){
echo $row->booking_id;
if($row->from_time <= $from_date && $row->to_date >= $to_date && $row->room_no = $room_no){
$message = "booking if works.";
echo "<script type='text/javascript'>alert('$message');</script>";
}else{ $message = "something wrong";
echo "<script type='text/javascript'>alert('$message');</script>";
}
}
$insert_post_sql = "INSERT INTO bookings
SET room_no='$room_no', hotel_id='$hotel_id', from_time='$from_date', to_time='$to_date', user_id='$user_id'
";
if ($mysqli->query($insert_post_sql)) {
$message = "hotel created - logging you out sir.";
echo "<script type='text/javascript'>alert('$message');</script>";
//header("location: index.php");
}else{
echo 'something went wrong';
Right now, it books the hotel, since the insert sql is not in the while loop. But when it checks, it never alerts the message "booking if works" - I just can't figure out how to construct it properly. All help is much appreciated!
