我需要从数据库中获取使用PHP和MySQL的日期和日期。我的表格如下:
db_day:
day_id day_name
1 Monday
2 Tuesday
3 Wednsday
4 Thursday
5 Friday
6 Saturday
7 Sunday
上面是我的日程表。
db_special:
id restname date_from date_to day_from day_to
1 aaa 2016-09-22 2016-09-26 1 4
2 bbb 2016-10-19 2016-10-28 2 5
3 ccc 2016-10-18 2016-10-25 4 7
以上是我的两个表格。假设用户有输入Tuesday
,并且需要从上面的table.Here中获取日期范围内的值。如果用户只有Tuesday
输入,则首先应该计算日期和日期。如果假设今天是Friday
,它将考虑下一个Tuesday
。
在这里,用户输入只有Tuesday
,所以它将计算今天的日期e.g-2016-10-21
,然后从上面的表中筛选出值。根据此示例,预期结果应为表中的以下行:
bbb 2016-10-19 2016-10-28 2 5
如何编写查询来解决此问题?
发布于 2016-10-21 15:28:59
您可以尝试使用以下代码:
// $db is PDO instance
// $userDay is user input day
$todayDayId = date('N');
$stmt = $db->prepare('SELECT day_id FROM db_day WHERE day_name=:userDay');
$stmt->execute([':userDay' => $userDay]);
$userDayId = $stmt->fetchColumn();
if($todayDayId > $userDayId) {
$diff = (7-$todayDayId) + $userDayId;
}
else {
$diff = $userDayId - $todayDayId;
}
$wantedDate = date('Y-m-d', strtotime('+'.$diff.' day'));
$pdoData = [
':wantedDate' => $wantedDate,
':wantedDate2' => $wantedDate,
];
$stmt = $db->prepare('SELECT id, restname, date_from, date_to, day_from, day_to
FROM db_special
WHERE date_from>=:wantedDate AND date_to<=:wantedDate2');
$stmt->execute($pdoData);
$result = $stmt->fetchColumn();
https://stackoverflow.com/questions/40169969
复制相似问题