房间和可用日期 PHP/MySQL [英] Rooms & Availability Dates PHP/MySQL

问题描述

所以，我正在设计一个适用于酒店的 Web 应用程序，它最终应该成为一种简单的方法，可以通过一些额外的代码为每个酒店网站进行安装.该应用程序使用 php 和 mysql.所以，正如您可能猜到的，我对数据库结构很困惑.尽管如此，我已经做了一些研究，而且我不只是要求一个成熟的预订系统.

So, I'm desinging a web application that is going to work for a Hotel and it should eventually become an easy way to install for every hotel website with some extra code. The application is working with php and mysql. So, as you probably guessed, I'm pretty confused with the database structure. Though, I've made some research and I'm not just asking for a full-fledged system for bookings.

因此，酒店老板必须拥有多种类型的房间.所以我想可能一张房间的桌子是必须的".我们开始吧:

So, owner of the hotel has to have many types of rooms. So probably a table for rooms is a "must" I suppose. Here we go:

---------- Rooms Table (example)

ID     NAME         ADULTS     ROOM_COUNT     PRICE/NIGHT     

1      DoubleRoom   2          5              45$
2      Family       3          2              75$
3      Single       1          3              35$

这很简单.可以通过我的应用程序添加房间，我还会附上适当的描述以及每个房间的各种图片.但让我们回到正题.人们应该这样预订......

This is pretty straightforward. Rooms can be added through my application and I'll also attach a proper description as well as a variety of images for each room. But let's get back to the point. People should make bookings so...

---------- Bookings Table (example)

ID    ROOM_ID      BOOKING_USER     CHECKIN (timestamp)   CHECKOUT (timestamp)

1     2            A full name      208420842042          308230482042824
2     3            A full name      208423242525          529752972595272
...

所以，这是 -希望- 数据库结构.现在，我正在考虑一种检查可用性的方法.我知道，这听起来可能很愚蠢，但我想不出一个聪明而简单的方法.在网站的形式中，应该有以下字段:check-in date 和 check-out-date 然后它们将被转换为时间戳和..这里是我坚持的时间！

So, this is -hopefully- the database structure. Now, I'm thinking of a way to check for availability. I know, it might sound stupid but I can't figure out a smart and easy way. In the form of the website, there should be the following fields: check-in date and check-out-date which will be then transformed to timestamps and.. well here's the time I stuck!

当我只有上面的表格时，如何检查房间的可用性.有什么办法或者我需要改变我的数据库结构吗?即使我设法检查了空房情况，房间总数 (ROOM_COUNT) 如何计算?我的意思是，我如何检查多种类型房间的可用性?我想过这样的事情:

How can I check for availabily of rooms when I only have the above tables. Is there any way or I need to change my database structure? Even if I manage to check for availability, how comes the total number of rooms (ROOM_COUNT) in count? I mean, how can I check availability for multiple types of rooms? I thought something like:

SELECT count(ROOM_ID) FROM bookings WHERE
bookings.checkin < DATE_GIVEN_AS_CHECKIN_INPUT and
bookings.checkout > DATE_GIVEN_AS_CHECKOUT_INPUT

然后根据此查询的输出，我会知道是否有可用房间.但是房间数呢?有没有更好的办法?

then depending on the output of this query I would know if there's a room available or not. But what about room count? Is there any better ways?

如果这里有人能给我启发，我会很高兴.提前致以最诚挚的问候和感谢，即使是阅读本文！
布兰

I'd be really glad if someone here could enlight me on this. Best regards and thanks in advance, even for reading this!
Blane

附言哦，这不是家庭作业！:P 反正我不去上学 :D

P.S. Oh, it's not a homework! :P I don't go to school anyway :D

推荐答案

以下查询将查找所有没有预订的房间，或者在入住/退房输入之前或之后的预订.

the following query will find all rooms which have no bookins, or bookings which are before or after the checkin/checkout input.

SELECT r.*
FROM rooms r
LEFT JOIN bookins b ON b.room_id = r.id
WHERE b.id IS NULL
OR (b.checkout >= DATE_GIVEN_AS_CHECKIN_INPUT AND b.checkin >= DATE_GIVEN_AS_CHECKOUT_INPUT)
OR (b.checkout <= DATE_GIVEN_AS_CHECKIN_INPUT AND b.checkout <= DATE_GIVEN_AS_CHECKOUT_INPUT)

要获得房间数量，只需将 SELECT r.* 替换为 SELECT COUNT(DISTINCT r.id)

to get the room count just replace SELECT r.* with SELECT COUNT(DISTINCT r.id)

这篇关于房间和可用日期 PHP/MySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！