I am trying to create a hotel room booking app .... I have created two tables
the room table which consists of roomno(PK), room type and room price
the availability table which consists of roomno(FK), room type, check-in-date and check-out-date
the PHP code I have created so far allows me to check the availability for a particular type of room for the given dates ...
$type = $_POST['pincode'];
$cin = $_POST['cin'];
$cout = $_POST['cout'];
$sql="select * from avalability where type = '$type' and cout>='$cin' and cin<='$cout'";
$res=mysqli_query($con,$sql);
$check=mysqli_fetch_array($res);
if(isset($check))
{
echo "rooms not available";
}
else
{
echo "rooms available";
$insert="insert into login.avalability(type,cin,cout) values('$type','$cin','$cout')";
mysqli_query($con,$insert);
}
mysqli_close($con);
?>
Now my question: suppose I have 5 single rooms in my room table and one single room has been booked for say 23rd march to 27th march, this makes the other four rooms free ...
But the code I have given doesn't work for this problem ... Can anybody please give me the right query to check room availability properly ?