PHP / SQL-如何检查日期用户输入是否在数据库中现有日期范围之间？ [英] PHP/SQL - How can I check if a date user input is in between an existing date range in my database?

问题描述

我正在尝试创建一个需要签入和签出日期以确认是否有空房的搜索页面。如果某个房间已有预订，并且其日期范围与用户输入重叠，则不会输出该房间。否则，如果它没有预订或它的当前预订与用户输入不重叠，则将其输出。

我当前的SQL仅根据床位数输出所有房间。出于某些原因，它不检查日期重叠。

  $ sql13 =选择从房间
选择的床位，方向，价格床位= $ nOfBeds和rooms.rid不在
中（从预订
中选择bookings.rid，而在$ c $登记和结帐之间或
之间（$ cInDate< =登记和$ cOutDate> =结帐））； 
 
 if（$ rOrientation！="））{
 $ sql13。=" AND direction =‘$ rOrientation’； 
} 
 $ results13 = mysqli_query（$ conn，$ sql13）
或死亡（查询问题。mysqli_error（$ conn））； 
  

房间更多。我只是拍了一个较小的屏幕快照，以使问题变小

解决方案

检查时间段的重叠非常简单。

让 b1 和 e1 代表时期1的开始和结束，其中 b1 在 e1

之前

让 b2 和 e2 代表第2期的开始和结束，其中 b2 之前 e2

如果时间段中没有重叠，那么我们知道以下内容将返回TRUE

 （b1> e2或b2> e1）
  

（基本上，一个时期的开始将是 在另一时期的结束之后。）

考虑到问题中的时间段仅由日期（没有时间成分）定义，假设一个预订时间段的结帐时间在随后的预订时间段的结帐时间之前足够，因此r过度劳累且薪水低的家政人员打扫房间...那么我们将不会认为入住日期等于另一个期间的退房日期是重复的，因此，这将不被认为是重复的

 （b1> = e2或b2> = e1）
   重叠，则该条件的否定将返回TRUE 
 不（b1> = e2或b2> = e1）
  
查找指定时间段内可用的房间（即没有重叠的预订），我们可以使用反加入模式：
  SELECT r.rid 
，r.beds 
，r.orientation 
，r.price 
从房间开始r 
左
加入预订b 
 ON b.rid = r.rid 
 AND NOT（？> = b.checkout或？< = b.checkin）
在哪里b.rid是NULL 
 AND r.beds =？ 
  
问号是按此顺序签入，签出和床位数的占位符。 （出于两个原因，我们倾向于将准备好的语句与绑定占位符一起使用...以避免避免产生不必要的SQL Injection漏洞，并且在某些数据库中可能会提高性能。）但是请随时在SQL中替换那些问号占位符
 
 
 
理解反联接的方式...外部联接返回房间中的所有行以及任何匹配的重叠预订。如果预订中没有匹配（重叠）行，则将返回房间中的行，其中包含预订列中的所有NULL值。因此，我们可以通过WHERE子句中的简单测试将所有至少具有匹配（重叠）行的房间排除在预订之外。如果存在匹配的行，我们知道预订中的 rid 列将具有非NULL值。如果我们排除所有具有非NULL值的行，则会留下没有重复预订的房间。
 
I am trying to create a search page that takes a check in and check out date to check if a room is available. if a room has an existing booking and its date range overlaps with the user input then it is not outputted. otherwise if it does not have a booking or its current bookings does not overlap with user input then it is outputted. A check in can exist on another booking's check out date
My current SQL outputs all rooms based on the number of beds only. for some reason it does not check for date overlaps.
$sql13 = "SELECT rid, beds, orientation, price FROM rooms 
          WHERE beds = $nOfBeds AND rooms.rid NOT IN 
         (SELECT bookings.rid FROM bookings 
          WHERE $cInDate BETWEEN checkin AND checkout OR 
         ($cInDate <= checkin AND $cOutDate >= checkout))";

if ($rOrientation != "") {
    $sql13 .= " AND orientation = '$rOrientation'";
}
$results13 = mysqli_query($conn, $sql13)
or die ('Problem with query' . mysqli_error($conn));


there are more rooms. I just took a smaller screenshot to make the question smaller
解决方案
It's pretty simple to check for an overlap of time periods.

Let b1 and e1 represent start and end of period 1, with b1 before e1

Let b2 and e2 represent start and end of period 2, with b2 before e2

If there is no overlap in the periods, then we know that the following will return TRUE
 ( b1 > e2 OR b2 > e1 )
(basically, the beginning of one period will be after the end of the other period.)

Given that the periods in the question are defined by just dates (with no time components), assuming that "checkout time" of one booking period will be sufficiently before "checkin time" of a subsequent booking period, giving enough time between for the overworked and underpaid housekeeping staff to clean the room... then we wouldn't consider a checkin date equal to a checkout date of another period to be an overlap... so equals wouldn't be a considered an overlap
 ( b1 >= e2 OR b2 >= e1 )
Conversely, if there is an overlap in the periods, then the negation of that condition would return TRUE
 NOT ( b1 >= e2 OR b2 >= e1 )
To find "rooms" available for a specified period (i.e. no overlapping bookings), we could use an anti-join pattern: 
  SELECT r.rid
       , r.beds
       , r.orientation
       , r.price
    FROM rooms r
    LEFT
    JOIN bookings b
      ON b.rid = r.rid
     AND NOT ( ? >= b.checkout OR ? <= b.checkin )
   WHERE b.rid IS NULL
     AND r.beds = ?
The question marks are placeholders for checkin, checkout and number of beds, in that order. (We prefer to use prepared statements with bind placeholders, for a couple of reasons... to avoid creating unnecessary SQL Injection vulnerabilities, and in some databases, potential for improved performance.) But feel free to substitute those question mark placeholders in the SQL text with appropriately validated and properly escaped literal expressions.

The way to understand the anti-join... the outer join returns all rows from rooms, along with any matching overlapping bookings. If there are no "matching" (overlapping) rows from bookings, the row from rooms will be returned, with all NULL values for the columns from bookings. So we can exclude all rooms that had at least one matching (overlapping) row from booking with a simple test in the WHERE clause.  If there was a matching row, we know that the rid column from booking would have a non-NULL value. If we exclude all rows with non-NULL values, we are left with rooms that didn't have an overlapping booking.

                        
这篇关于PHP / SQL-如何检查日期用户输入是否在数据库中现有日期范围之间？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！