在2个日期之间循环，每次循环添加一天-Java 学习之路

我想知道以下是否可行 . 我有两个约会，让我们说：

2015年11月20日和25/11/15

我有一个SQL查询，我需要在第一个日期之前的5天到最后一个日期之后的5天，每天的2个日期之间进行搜索 . 我的简单陈述是：

$result = mysqli_query($con,"SELECT USER_ID, ROOM, DATE 
FROM booking_table WHERE DATE BETWEEN 'DATE1' AND 'DATE2'");
while($row = mysqli_fetch_array($result))
{
print "<tr><td>" . $row['DATE'] . "<td>" . $row['ROOM'] . "</td><td>" . $row['USER_ID'] . "</td></tr>";
}

现在我希望这循环使用以下日期替换语句中的DATE1和DATE2：

15/11/2015 AND 16/11/2015 (5 days before 1st date and the next day)
16/11/2015 AND 17/11/2015

等到最后一个日期后的5天，所以：

29/11/2015 AND 30/11/2015

因此，我首先假设我需要在5天之前获取日期，然后添加一天，这将在两天之间获得前两天，然后继续每天添加一天，直到最后一天之后的5天 . 我不知道该怎么做 . 非常感谢您的帮助 .

最终结果应如下所示：

| Date       | Room Type     | User ID
| 15/11/2015 | Double        | 3
| 15/11/2015 | Twin          | 4
| 16/11/2015 | Shared Double | 9
| 24/11/2015 | Single        | 6

等等...

2 回答

你可以使用

For @Days = 0 to ?? Step 5
    ...
next

循环查询和

WHERE DATE > ('DATE1',INTERVAL @Days DAY) AND DATE < ('DATE2',INTERVAL @Days DAY)

（我不喜欢）

否则你可以使用这个技巧：

for ($date = strtotime("2014-01-01"); $date < strtotime("2014-02-01"); $date = strtotime("+1 day", $date)) {
echo date("Y-m-d", $date)."
";
}

回复于 2024-05-05T02:58:50+08:00

创建日期的另一个选项是使用DateTime，DateInterval和DatePeriod .

首先，您可以使用示例中的日期创建 $date1 和 $date2 ：'20/11/2015'和'25/11/15' .

您可以使用DateTime类上的 modify 函数来添加和减去5天 .

然后创建 $dateInterval 为1天作为dateperiod的间隔 . 然后创建一个 $datePeriod ，您可以使用 foreach 构造循环 .

例如：

$date1 = DateTime::createFromFormat('d/m/Y', '20/11/2015');
$date1->modify('-5 days');

$date2= DateTime::createFromFormat('d/m/y', '25/11/15');
$date2->modify('+5 days');

$dateInterval = new DateInterval('P1D');
$datePeriod = new DatePeriod(
    $date1, $dateInterval ,$date2
);

foreach ($datePeriod as $date) {
    $sql = sprintf("SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '%s' AND '%s'",
        $date->format("d/m/Y"),
        $date->modify('+1 days')->format("d/m/Y")
    );
    echo "$sql<br>";
}

这将使用日期回显查询：

SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '15/11/2015' AND '16/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '16/11/2015' AND '17/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '17/11/2015' AND '18/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '18/11/2015' AND '19/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '19/11/2015' AND '20/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '20/11/2015' AND '21/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '21/11/2015' AND '22/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '22/11/2015' AND '23/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '23/11/2015' AND '24/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '24/11/2015' AND '25/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '25/11/2015' AND '26/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '26/11/2015' AND '27/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '27/11/2015' AND '28/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '28/11/2015' AND '29/11/2015'
SELECT USER_ID, ROOM, DATE FROM booking_table WHERE DATE BETWEEN '29/11/2015' AND '30/11/2015'

回复于 2024-05-05T02:58:50+08:00

在2个日期之间循环，每次循环添加一天

2 回答

相关问题