PHP和MySQL：将月份和日期与动态年份进行比较 [英] PHP and MySQL: Comparing month and day with dynamic year

问题描述

我正在处理的应用程序遇到了麻烦，希望获得一些指导。

I am having a bit of trouble with an application I am working on and I hope to get some guidance.

我有一个简单的预订系统：每个项目都有标准价格，但在某些时期价格可能会发生变化。

I have a simple booking system: each item has a standard price, but there are some periods when the price can change.

例如：

• 标准价格为 100 /天

来自10月。 1月至 2月。每年1st ，价格为 80 /天

From Oct. 1st to Feb. 1st of each year, the price is 80/day

从起俊1st 至 8月。每年31日的$ 价格为 120 /天

[...] 最多6个不同的价格变化（季节）

因为这是每年一次的活动，所以我只需要月和日，但是我不知道如何在数据库中存储这些值。我正在考虑有两个字段，一个字段用于保存月份，一个字段用于保存数值（ 01-12 表示月份， 01-31 ）。这些值由用户自己设置，并且与商品本身相关，并非所有人都固定。

Because this is a yearly occurrence, I only need the month and day, but I dont't know how to store these values in the database. I was thinking of having two fields, one for month and one for day which hold numeric values(01-12 for month, 01-31 for day). These values are set by the user himself and they are relative to the item itself, it's not fixed for everyone.

问题是当有人进行预订时，我需要检查价格是否需要更改，而我不知道如何构建查询来进行检查。为了使事情更糟，预订期间的价格可能有多个值。

The problem is that when someone makes a booking, I need to check if the price needs to be changed and I don't understand how to build the query to check this. To make maters worst, the price can have multiple values for a booked period.

按照上面的示例，如果我要预订中的项目九月第一（当前年）至 7月。第一年（第二年），价格如下：

Following the example above, If I were to book an item from Sep. 1st (current year) to Jul. 1st (next year), the price will be as follow:

• 100 /一天（ 9月）。 1st 至 9月。 10月30日

80 /天。 1月至 2月。 1st

每天100次。到5月31日的第二次

100/day from Feb. 2nd to May 31st

每天120 Jun的 strong。 1st 至 7月。 1st

120/day from Jun. 1st to Jul. 1st

有什么方法可以检查这些情况而不必每天循环

Is there any way of checking these cases without having to loop each day and checking against the database for a match ?

谢谢

推荐答案

我不知道您的数据库表，但我想您可以这样：

I don't know your database tables, but I think you could do like this:

1. 获取期间，iniDay，iniMonth，endDay， endMonth


2. 您的表需要具有以下字段：ini_day，ini_month，ini_daymonth，end_day，end_month，end_daymonth，price_day


3. 从表
中选择*在iniDay> = booking_ini_day AND iniMonth> = booking_ini_month-获得初始期间
和iniDay< = booking_end_day AND iniMonth< = booking_end_month
UNION ALL
从表
中选择* WHERE iniDay< = booking_ini_day AND iniMonth< = booking_ini_month-获取中间期间
AND endDay> = booking_end_day AND endMonth> = booking_end_month
UNION ALL
选择*从表
中
endDay> = booking_ini_day AND endMonth> = booking_ini_month
AND endDay< = booking_end_day AND endMonth< = booking_end_month-获取结束时间


4. 计算结果查询中每个期间/行的天数并显示给用户。

1. Get your period, iniDay, iniMonth, endDay, endMonth
2. Your table need to have these fields: ini_day, ini_month, ini_daymonth, end_day, end_month, end_daymonth, price_day
3. SELECT * from table WHERE iniDay >= booking_ini_day AND iniMonth >= booking_ini_month -- get initial period AND iniDay <= booking_end_day AND iniMonth <= booking_end_month UNION ALL SELECT * from table WHERE iniDay <= booking_ini_day AND iniMonth <= booking_ini_month -- get middle periods AND endDay >= booking_end_day AND endMonth >= booking_end_month UNION ALL SELECT * from table WHERE endDay >= booking_ini_day AND endMonth >= booking_ini_month AND endDay <= booking_end_day AND endMonth <= booking_end_month -- get end period
4. Count days for each period/row in your result query and show to your user.

您将复制数据库信息，例如日，月和日月，但这是为了提高性能。

You will "duplicate" database information, like day, month and daymonth, but it's for better performance.

这篇关于PHP和MySQL：将月份和日期与动态年份进行比较的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！