将mysql数据库中的值显示到日历中

rnmwe5a2  于 2021-07-26  发布在  Java
关注(0)|答案(1)|浏览(357)

我现在有一个mysql数据库，里面有预订。它有到达日期和离开日期。我正在寻找一种方法，让它显示从每个预订到日历中的名字，在他们逗留期间的所有日子，所以它可以很容易地查看。至少一个关于如何处理这一问题的提纲将不胜感激。
谢谢！
目前，我有以下数据来计算他们的所有逗留天数：

$period = new DatePeriod(
    new DateTime($row['ArrivalDate']),
    DateInterval::createFromDateString('+1 day'),
    new DateTime($row['DepartureDate'])
);                    
foreach ( $period as $dt ) {
    echo $dt->format( 'd-m-Y' ) . "\n";
}
sqlphpHtmlCalendar

1条答案

按热度按时间
8oomwypt

8oomwypt1#

初始样本数据：

+----+------------+-------------+---------------+
| id | name       | arrivalDate | departureDate |
+----+------------+-------------+---------------+
|  1 | John Smith | 2020-07-17  | 2020-07-20    |
|  2 | John Doe   | 2020-07-18  | 2020-07-22    |
|  3 | Jack Smith | 2020-07-28  | 2020-08-02    |
+----+------------+-------------+---------------+
3 rows in set (0,00 sec)

概要是：您可以每月通过一次sql查询来获取所需的数据，但我采用了不同的方法。在php中，获取要显示的月份（如果未提供，则为当前月份）。算出那个月的第一天和最后一天——做一个句号。对于期间的每一天，获取当天有效的预订。现在您已经拥有了一个月中的所有日期以及每天的所有预订，您可以根据需要显示它。还可以计算上一个月和下一个月并显示此信息，以便您可以浏览下一个月/上一个月。
我是作为一个脚本来做的，它接受一个月作为参数，如果没有提供参数，它将显示当前月份。

<?php

// database and query
$db = new PDO("mysql:host=localhost;dbname=calendar", 'z', 'z');

function selectFromDate(DateTime $date): array {
  global $db;

  $q = $db->prepare("SELECT * FROM reservations WHERE arrivalDate <= :date AND departureDate >= :date");
  $q->execute([':date' => $date->format('Y-m-d')]);

  return $q->fetchAll(PDO::FETCH_OBJ);
}

// initial dates
$yearMonthDate = new DateTime();
if ($argc > 1) {
  $yearMonthDate = new DateTime($argv[1]);
}
$yearMonth = $yearMonthDate->format('Y-m');
$nextYearMonth = (clone $yearMonthDate)->modify('first day of next month')->format('Y-m');
$previousYearMonth = (clone $yearMonthDate)->modify('first day of previous month')->format('Y-m');
$firstDayDate = (clone $yearMonthDate)->modify('first day of this month');
$lastDayDate = (clone $yearMonthDate)->modify('last day of this month');
$monthPeriod = new DatePeriod($firstDayDate, new DateInterval('P1D'), $lastDayDate);

// fetch data
$monthData = [];
foreach ($monthPeriod as $date) {
  $dayInfo = new stdClass();
  $dayInfo->date = $date->format('Y-m-d');
  $dayInfo->reservations = [];

  foreach (selectFromDate($date) as $reservation) {
    $dayInfo->reservations[] = $reservation->name;
  }
  $monthData[] = $dayInfo;
}
var_dump($monthData);

// display
echo 'Displaying: '.$yearMonth.PHP_EOL;
foreach ($monthData as $day) {
  echo '  '.$day->date.': '.join($day->reservations, ', ');
  echo PHP_EOL;
}

echo 'Previous month: '.$previousYearMonth. ', next month: '.$nextYearMonth.PHP_EOL;

你这样称呼它： file.php 并得到如下输出（无参数-当月输出）：

Displaying: 2020-07
  2020-07-01: 
  2020-07-02: 
  2020-07-03: 
  2020-07-04: 
  2020-07-05: 
  2020-07-06: 
  2020-07-07: 
  2020-07-08: 
  2020-07-09: 
  2020-07-10: 
  2020-07-11: 
  2020-07-12: 
  2020-07-13: 
  2020-07-14: 
  2020-07-15: 
  2020-07-16: 
  2020-07-17: John Smith
  2020-07-18: John Smith, John Doe
  2020-07-19: John Smith, John Doe
  2020-07-20: John Smith, John Doe
  2020-07-21: John Doe
  2020-07-22: John Doe
  2020-07-23: 
  2020-07-24: 
  2020-07-25: 
  2020-07-26: 
  2020-07-27: 
  2020-07-28: Jack Smith
  2020-07-29: Jack Smith
  2020-07-30: Jack Smith
Previous month: 2020-06, next month: 2020-08

的内容 $monthData :

array(30) {
  [0]=>
  object(stdClass)#8 (2) {
    ["date"]=>
    string(10) "2020-07-01"
    ["reservations"]=>
    array(0) {
    }
  }
  [1]=>
  object(stdClass)#7 (2) {
    ["date"]=>
    string(10) "2020-07-02"
    ["reservations"]=>
    array(0) {
    }
  }
  [2]=>
  object(stdClass)#9 (2) {
    ["date"]=>
    string(10) "2020-07-03"
    ["reservations"]=>
    array(0) {
    }
  }
  [3]=>
  object(stdClass)#10 (2) {
    ["date"]=>
    string(10) "2020-07-04"
    ["reservations"]=>
    array(0) {
    }
  }
  [4]=>
  object(stdClass)#11 (2) {
    ["date"]=>
    string(10) "2020-07-05"
    ["reservations"]=>
    array(0) {
    }
  }
  [5]=>
  object(stdClass)#12 (2) {
    ["date"]=>
    string(10) "2020-07-06"
    ["reservations"]=>
    array(0) {
    }
  }
  [6]=>
  object(stdClass)#13 (2) {
    ["date"]=>
    string(10) "2020-07-07"
    ["reservations"]=>
    array(0) {
    }
  }
  [7]=>
  object(stdClass)#14 (2) {
    ["date"]=>
    string(10) "2020-07-08"
    ["reservations"]=>
    array(0) {
    }
  }
  [8]=>
  object(stdClass)#15 (2) {
    ["date"]=>
    string(10) "2020-07-09"
    ["reservations"]=>
    array(0) {
    }
  }
  [9]=>
  object(stdClass)#16 (2) {
    ["date"]=>
    string(10) "2020-07-10"
    ["reservations"]=>
    array(0) {
    }
  }
  [10]=>
  object(stdClass)#17 (2) {
    ["date"]=>
    string(10) "2020-07-11"
    ["reservations"]=>
    array(0) {
    }
  }
  [11]=>
  object(stdClass)#18 (2) {
    ["date"]=>
    string(10) "2020-07-12"
    ["reservations"]=>
    array(0) {
    }
  }
  [12]=>
  object(stdClass)#19 (2) {
    ["date"]=>
    string(10) "2020-07-13"
    ["reservations"]=>
    array(0) {
    }
  }
  [13]=>
  object(stdClass)#20 (2) {
    ["date"]=>
    string(10) "2020-07-14"
    ["reservations"]=>
    array(0) {
    }
  }
  [14]=>
  object(stdClass)#21 (2) {
    ["date"]=>
    string(10) "2020-07-15"
    ["reservations"]=>
    array(0) {
    }
  }
  [15]=>
  object(stdClass)#22 (2) {
    ["date"]=>
    string(10) "2020-07-16"
    ["reservations"]=>
    array(0) {
    }
  }
  [16]=>
  object(stdClass)#23 (2) {
    ["date"]=>
    string(10) "2020-07-17"
    ["reservations"]=>
    array(1) {
      [0]=>
      string(10) "John Smith"
    }
  }
  [17]=>
  object(stdClass)#24 (2) {
    ["date"]=>
    string(10) "2020-07-18"
    ["reservations"]=>
    array(2) {
      [0]=>
      string(10) "John Smith"
      [1]=>
      string(8) "John Doe"
    }
  }
  [18]=>
  object(stdClass)#25 (2) {
    ["date"]=>
    string(10) "2020-07-19"
    ["reservations"]=>
    array(2) {
      [0]=>
      string(10) "John Smith"
      [1]=>
      string(8) "John Doe"
    }
  }
  [19]=>
  object(stdClass)#28 (2) {
    ["date"]=>
    string(10) "2020-07-20"
    ["reservations"]=>
    array(2) {
      [0]=>
      string(10) "John Smith"
      [1]=>
      string(8) "John Doe"
    }
  }
  [20]=>
  object(stdClass)#27 (2) {
    ["date"]=>
    string(10) "2020-07-21"
    ["reservations"]=>
    array(1) {
      [0]=>
      string(8) "John Doe"
    }
  }
  [21]=>
  object(stdClass)#26 (2) {
    ["date"]=>
    string(10) "2020-07-22"
    ["reservations"]=>
    array(1) {
      [0]=>
      string(8) "John Doe"
    }
  }
  [22]=>
  object(stdClass)#31 (2) {
    ["date"]=>
    string(10) "2020-07-23"
    ["reservations"]=>
    array(0) {
    }
  }
  [23]=>
  object(stdClass)#29 (2) {
    ["date"]=>
    string(10) "2020-07-24"
    ["reservations"]=>
    array(0) {
    }
  }
  [24]=>
  object(stdClass)#30 (2) {
    ["date"]=>
    string(10) "2020-07-25"
    ["reservations"]=>
    array(0) {
    }
  }
  [25]=>
  object(stdClass)#33 (2) {
    ["date"]=>
    string(10) "2020-07-26"
    ["reservations"]=>
    array(0) {
    }
  }
  [26]=>
  object(stdClass)#34 (2) {
    ["date"]=>
    string(10) "2020-07-27"
    ["reservations"]=>
    array(0) {
    }
  }
  [27]=>
  object(stdClass)#35 (2) {
    ["date"]=>
    string(10) "2020-07-28"
    ["reservations"]=>
    array(1) {
      [0]=>
      string(10) "Jack Smith"
    }
  }
  [28]=>
  object(stdClass)#36 (2) {
    ["date"]=>
    string(10) "2020-07-29"
    ["reservations"]=>
    array(1) {
      [0]=>
      string(10) "Jack Smith"
    }
  }
  [29]=>
  object(stdClass)#32 (2) {
    ["date"]=>
    string(10) "2020-07-30"
    ["reservations"]=>
    array(1) {
      [0]=>
      string(10) "Jack Smith"
    }
  }
}

如您所见，它是一个包含30个元素的数组（一个月中的每一天一个元素）。每个元素都包含一个日期和这一天的预订，所以现在可以很容易地根据需要显示它（例如html）。
如果你想看下个月的节目，可以这样说： file.php 2020-08 您将得到以下输出：

Displaying: 2020-08
  2020-08-01: Jack Smith
  2020-08-02: Jack Smith
  2020-08-03: 
  2020-08-04: 
  2020-08-05: 
  2020-08-06: 
  2020-08-07: 
  2020-08-08: 
  2020-08-09: 
  2020-08-10: 
  2020-08-11: 
  2020-08-12: 
  2020-08-13: 
  2020-08-14: 
  2020-08-15: 
  2020-08-16: 
  2020-08-17: 
  2020-08-18: 
  2020-08-19: 
  2020-08-20: 
  2020-08-21: 
  2020-08-22: 
  2020-08-23: 
  2020-08-24: 
  2020-08-25: 
  2020-08-26: 
  2020-08-27: 
  2020-08-28: 
  2020-08-29: 
  2020-08-30: 
Previous month: 2020-07, next month: 2020-09

如果你想要浏览器版本只需通过 YYYY-MM 通过链接参数的参数。如果要从日历中排除出发日期，请修改查询 departureDate > :date

赞(0）分享  回复(0）举报  2021-07-26
我来回答

相关问题

    查看更多

    热门标签

    更多
    JavaquerypythonNode开发语言requestUtil数据库Table后端算法LoggerMessageElementParser

    最新问答

    更多
    • 蜀ICP备13028337号-1 大数据知识库 https://www.saoniuhuo.com © All rights reserved
    • 本站内容来源互联网,如果侵犯您的权益请联系我们删除, 联系方式：448109455@qq.com