Determine if the store is Open or Close?

PHP
1

In PHP and MySQL - how to determine if the Store is Open or Close (return true or false)?

Also how to get the next opening hours if the store is closed?

Example of Opening_Hours table:


    +----+---------+----------+-----------+------------+---------+
    | id | shop_id | week_day | open_hour | close_hour | enabled |
    +----+---------+----------+-----------+------------+---------+
    |  1 |       1 |        1 | 16:30:00  | 23:30:00   |       1 |
    |  2 |       1 |        2 | 16:30:00  | 23:30:00   |       1 |
    |  3 |       1 |        3 | 16:30:00  | 23:30:00   |       0 |
    |  4 |       1 |        4 | 16:30:00  | 23:30:00   |       1 |
    |  5 |       1 |        5 | 10:00:00  | 13:00:00   |       1 |
    |  6 |       1 |        5 | 17:15:00  | 00:30:00   |       1 |
    |  7 |       1 |        6 | 17:15:00  | 01:30:00   |       1 |
    |  8 |       1 |        7 | 16:30:00  | 23:30:00   |       0 |
    +----+---------+----------+-----------+------------+---------+

The open_hour and close_hour are TIME type fields. Is the table design ok?

If the current times are:

  • Current time: Tue 23:00

  • Output: Status - Open (Open at Tue 16:30 - 23:30)

  • Current time: Tue 23:40

  • Output: Status - Close (Open at Thur 16:30 - 23:30)

Open on Thursday because Opening_Hours.week_day = 3 is disabled

Now how to handle the midnight time? This get more complicated.

As you can see, on Saturday (Opening_Hours.week_day = 5), it is open from 17:15 PM to 01:30 (closed next day Sunday)

If the current time is Sunday 01:15 AM, then the store would still be open base on Opening_Hours.week_day = 5

  • Output: Status - Open (Open at Sat 17:15 - 01:30)
2

What have you got so far?

Have you at least got a query which turns PHPs date(‘N’) and date(‘H:i:s’) into a valid query which can at least detect todays day number and present (server) time?

If so, show it … if not, try and create it…

3 

<?php

$sql = "
	SELECT
		  CASE
			WHEN TIME(NOW()) BETWEEN open_hour AND close_hour
				THEN 'Open'
			ELSE 'Closed'
		  END AS 'Open/Closed'
		, null AS WeekDay
		, null AS OpenHour
		, null AS CloseHour	
	FROM Opening_Hours
	WHERE DAYOFWEEK(NOW()) = week_day
	
	UNION ALL
	
	SELECT
		  null
		, N.week_day
		, N.open_hour
		, N.close_hour
	FROM Opening_Hours AS N
	WHERE N.week_day > DAYOFWEEK(NOW())
		AND N.enabled = 1
	LIMIT 2
";
$output = array();
foreach ( db()->query($sql) as $r ) {
	$output[] = $r;
}
echo 'Status - '.$output[0]['Open/Closed'].' (Open at '.yourDateFunction($output[1]['WeekDay']).' '.$output[1]['OpenHour'].'  to '.$output[1]['CloseHour'].')';

Slightly tested…

Now how to handle the midnight time? This get more complicated.

As you can see, on Saturday (Opening_Hours.week_day = 5), it is open from 17:15 PM to 01:30 (closed next day Sunday)

If the current time is Sunday 01:15 AM, then the store would still be open base on Opening_Hours.week_day = 5

  • Output: Status - Open (Open at Sat 17:15 - 01:30)

For this, I would split it up in the database - Saturday 17:15 - 24:00, then Sunday 00:00 - 01:30 since technically you are open on Sunday, only during those hours.

4

That second select needs an ORDER BY. Think about it :slight_smile:

Yes, that’s what I would do as well – just makes the problem so much easier :slight_smile:

5

Thanks, I did mention that I slightly tested it out…