I do get open shops but when I want to print closed shops the query print open shop as closed. My Goal is to make the same query to print open and closed shops because now I have two different queries.
What is difficult for me is that restaurants could be open two times per day. For example Restuarant, open Monday at 10 am till Tuesday 03 am and Tuesday at 10 am till Wednesday 03 am.
I tried to make a CASE WHEN query but the problem was it could not get restaurant ID with printed status.
My business_hour table with two inserted restaurants NOTE the restaurantID is a foreign key of a restaurant table.
id,restaurantID,openday,opentime,closeday,closetime
1,15,Monday,10:00:00,Tuesday,03:00:00
2,15,Tuesday,10:00:00,Wednesday,03:00:00
3,24,Monday,10:00:00,Tuesday,03:00:00
4,24,Tuesday,10:00:00,Wednesday,03:00:00
5,24,Wednesday,10:00:00,Thursday,04:00:00
6,24,Thursday,10:00:00,Friday,02:00:00
7,24,Friday,10:00:00,Saturday,01:00:00
8,24,Saturday,11:00:00,Sunday,01:00:00
9,24,Sunday,11:00:00,Monday,02:00:00
The first below query print open restaurant perfectly. How I get closed restaurants?
query for checking open restaurants
$stmt = $pdo->prepare("SELECT * FROM
business_hour
INNER JOIN restaurant
ON business_hour.restaurantID =
restaurant.id
WHERE
(closeday = '$today' AND closetime >=
'$timeNow')
OR
(openday = '$today' AND opentime <=
'$timeNow')
");
$stmt->execute();
$i=0;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row['ravintolaID'].' '.'OPEN'.' '.$row['openday'].' '.$row['opentime'].' --'.$row['closeday'].' '.$row['closetime'].' '.'<br>';
$i++;
}
I tried this query
query for checking closed restaurants
$stmt = $pdo->prepare("SELECT * FROM business_hour
INNER JOIN restaurant
ON business_hour.restaurantID = restaurant.id
WHERE
(closeday = '$today' AND closetime < '$timeNow')
OR
(openday = '$today' AND opentime > '$timeNow')
");
$stmt->execute();
$i=0;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
echo $row['ravintolaID'].' '.'CLOSED'.' open at '.$row['openday'].' '.$row['opentime'].' -- '.$row['closeday'].' '.$row['closetime'].' '.'<br>';
$i++;
}
I expect the output show me just one time closed and open restaurants.
The current day is Monday and Time is 7 am
OUTPUT
24 OPEN Sunday 10:00:00 --Monday 08:00:00
15 CLOSED open at Monday 10:00:00 -- Tuesday 03:00:00
15 CLOSED open at Sunday 10:00:00 -- Monday 03:00:00
24 CLOSED open at Monday 10:00:00 -- Tuesday 03:00:00
