我想使用PHP和MySQL每半小时显示商店可用性状态,
为此,我尝试了以下代码,为我创建时间插槽
$duration="30";
$start="10:00AM";
$end="07:00PM";
$start = new DateTime($start);
$end = new DateTime($end);
$start_time = $start->format('H:i');
$end_time = $end->format('H:i');
$i=0;
while(strtotime($start_time) <= strtotime($end_time)){
$start = $start_time;
$end = date('H:i',strtotime('+'.$duration.' minutes',strtotime($start_time)));
$start_time = date('H:i',strtotime('+'.$duration.' minutes',strtotime($start_time)));
$i++;
if(strtotime($start_time) <= strtotime($end_time)){
$time[$i]['start'] = $start;
$time[$i]['end'] = $end;
}
}
print_R($time);
上面的代码显示以下结果(正确创建时间插槽(
Array
(
[1] => Array
(
[start] => 10:00
[end] => 10:30
)
[2] => Array
(
[start] => 10:30
[end] => 11:00
)
...// and so on
我想要这样的结果
Array
(
[1] => Array
(
[start] => 10:00
[end] => 10:30
[status] => availiable
)
[2] => Array
(
[start] => 10:30
[end] => 11:00
[status] => booked
)
这是我的餐桌上的"预订" phpmyadmin
id shop_id date booking_start_time booking_close_time
1 3 4-7-2019 10:00 11:00
我该怎么做?预先感谢
您需要从数据库中比较时间插槽:
while(strtotime($start_time) <= strtotime($end_time)){
$start = $start_time;
$end = date('H:i',strtotime('+'.$duration.' minutes',strtotime($start_time)));
$start_time = date('H:i',strtotime('+'.$duration.' minutes',strtotime($start_time)));
$i++;
if(strtotime($start_time) <= strtotime($end_time)){
$time[$i]['start'] = $start;
$time[$i]['end'] = $end;
}
//Here you need to write query and fetch data.
$todayDate = date('d-m-Y'); //Please check date format. It should be similar as your database date field format.
//please use data binding instead of contacting variable.
$selectQuery = "select `id` from `booking` where date = "'.$todayDate.'" and
(( `booking_start_time` >= "'.$start.'" AND `booking_start_time` <= "'.$start.'" ) ||
(`booking_close_time` >= "'.$end.'" AND `booking_close_time` <= "'.$end.'")) ";
// After, you need to exeucte this query and need to check query output. if it has records, then you need to show booked else available. as below
$result = mysqli_query($con, $selectQuery);
if ($result->num_rows) {
$time[$i]['status'] = 'booked';
} else {
$time[$i]['status'] = 'availiable';
}
}
print_R($time);
希望它对您有帮助。
这是您查询的最佳解决方案。
select count(*) as aggregate from `bookings` where (`booking_date` = '$booking_date') and (( start_time >= '$start_time' AND end_time <= '$end_time' ) || (end_time >= '$start_time' AND start_time <= '$end_time'))