如何获得预订的可用日期? [英] How to get available dates with reservations?

问题描述

我正在尝试构建一个系统，该系统可以让用户预定一个项目，例如一台笔记本电脑.

I am trying to build a system which lets users reserve a item, let's say a laptop..

我的桌子看起来像这样:

My table looks like this:

Table 'items'
id int(20) PK auto_increment
type int(20) FK(types.id)
name varchar(200)

Table 'types'
id int(20) PK auto_increment
name varchar(200)

Table 'reservations'
id int(20) PK auto_increment
user int(20) FK(users.id)
startDate datetime
endDate datetime

让我们说我有1台(笔记本电脑)和3台笔记本电脑，它们都具有相同的规格，并且用户只想保留一台笔记本电脑.我将如何仅凭一台笔记本电脑获取所有可用日期?

Lets say I have 1 type (laptops) and 3 laptops all with the same specs, and a user just wants to reserve one laptop. How would I get all available dates for just a laptop ?

我不介意如何通过PHP或MySQL达到此目的，我只是打算让用户获得具有给定类型项目的可用日期的列表.

I dont mind how this is reached, either via PHP or MySQL, I just intend on letting users have a list with available dates for the given type of item..

推荐答案

如果您的预订表单提示您输入$ start和$ end日期/时间进行预订，则此查询将告诉您在这两个指定的时间段内哪些笔记本电脑可用日期:

If your reservation form prompts for $start and $end date/times for the reservation, this query will tell which laptops are available throughout the period specified by those two dates:

SELECT items.id, items.name
FROM items
LEFT JOIN types ON items.type = types.id
LEFT JOIN reservations ON reservations.item_id = items.id
WHERE (((reservations.endDate NOT between $start AND $end) and
      (reservations.startDate NOT between $start AND $end)) or
      (reservations.id IS NULL)) and
      (types.id = $laptop_type_id)

前两个between子句将查找开始/结束日期不符合请求的开始/结束日期的任何笔记本电脑-例如它们是保留的，但在指定的时间范围内可用.空检查将找到根本没有保留的所有笔记本电脑.并且type.id检查将搜索范围限制为仅笔记本电脑.

The first two between clauses will find any laptops whose start/end dates do not fall withing the requested start/end dates - e.g. they're reserved, but are available in the specified time range. The null check will find any laptops that not reserved at all. And the types.id check restricts the search to only laptops.

这篇关于如何获得预订的可用日期?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！