存储打开和关闭时间，打开时显示关闭

Question

I am trying to show if a shop is opened or closed, based on opening and closing times in my db. If it is open show the opening and closing times for that day, if it is closed echo closed. My problem at the moment is my query is echoing closed even when the shop is scheduled to be open (attempt 1) or is not echoing anything at all (attempt 2).

A closed store in my DB is represented as 00:00. Any suggestions or guidance would be greatly appreciated, as i am teaching myself and have come to a stand still.

DB

CREATE TABLE `Opening_hrs` (
`OH_ID` bigint(255) NOT NULL AUTO_INCREMENT,
`Restaurant_ID` bigint(255) NOT NULL,
`Day_of_week` int(11) NOT NULL,
`Open_time` time NOT NULL,
`Closing_time` time NOT NULL,
PRIMARY KEY (`OH_ID`),
KEY `Restaurant_ID` (`Restaurant_ID`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8

This was my first attempt

date_default_timezone_set("Europe/London");
  $output_ohr = '';

  $ohrs = mysqli_query($dbc, "SELECT * FROM Opening_hrs 
WHERE Restaurant_ID='$rest_id' AND Day_of_week = DATE_FORMAT(NOW(), '%w')
AND CURTIME() BETWEEN Open_time AND Closing_time");

echo var_dump($ohrs);

$count_ohrs = mysqli_num_rows($ohrs);
if ($count_ohrs === 0) {
    $output_ohr = '<b> Closed</b>';
} else {
    $i = 1;
}while ($row_ohr = mysqli_fetch_array($ohrs )) {
    $o_time = $row_ohr['Open_time'];
    $c_time = $row_ohr['Closing_time'];



    $output_ohr = $output_ohr . '<p>Open</p>' .
            '<p>' .$o_time. ' - ' .$c_time. '</p>' 
    ;
    $i++;
 }

My second attempt

  date_default_timezone_set("Europe/London");

  $closed= strtotime("00:00am today GMT");
  $output_ohr = '';

  $ohrs = mysqli_query($dbc, "SELECT * FROM Opening_hrs 
WHERE Restaurant_ID='$rest_id' AND Day_of_week = DATE_FORMAT(NOW(), '%w')
AND CURTIME() BETWEEN Open_time AND Closing_time");

  echo var_dump($ohrs);

    $i = 1;
   while ($row_ohr = mysqli_fetch_array($ohrs )) {
    $o_time = $row_ohr['Open_time'];
    $c_time = $row_ohr['Closing_time'];

   if($o_time === $closed){
   $output_ohr = '<p>closed</p>';
  }else{

    $output_ohr = $output_ohr . '<p>Open</p>' .
            '<p>' .$o_time. ' - ' .$c_time. '</p>' 
    ;
    $i++;
      }
  }       

Answer 1

In your second attempt you are selecting only the open shops. If the shop is closed, the query won't return any rows and thus the while block does not run even once.

Use the following code to see if a shop is open or closed:

$stmt = mysqli_prepare($dbc, "SELECT COUNT(*) FROM Opening_hrs 
WHERE Restaurant_ID=? AND Day_of_week = DATE_FORMAT(NOW(), '%w')
AND CURTIME() BETWEEN Open_time AND Closing_time");

$stmt->bind_param('i', $rest_id);
$stmt->execute();
$stmt->bind_result($result);
$stmt->fetch();

if ($result) {
    echo 'Open';
} else {
    echo 'Closed';
}

(Note the usage of prepared statements that prevent a possible SQL injection.)