CODEWITHSUNDEEP
phpmysqldatabase-designstrtotime
user1246800
user1246800

Reputation: 287

Determine if the store is open?

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. Table design ok?

Example of current times:

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: Open, 'Open at Sat 17:15 - 01:30'

Upvotes: 7

Views: 811

Answers (2)

Ynhockey
Ynhockey

Reputation: 3932

You can use the PHP date() function and compare it to your opening hours.

You can do something like this recursive function (not working PHP code, but PHP combined with pseudo-code):

/* $current_time should be in the format of date("His") */
function check_hours($current_day, $current_time)
{
    Get the MySQL row for today here

    if (Opening_Hours.enabled == 1 WHERE Opening_Hours.week_day == $current_day)
    {
        if ((date("His") >= Opening_Hours.open_hour) and ($current_time <= Opening_Hours.close_hour))
        {
            // convert_numeric_day_to_full_representation isn't a real function! make one
            return 'Open: ' . convert_numeric_day_to_full_representation($current_day) . ' ' . Opening_Hours.open_hour . ' – ' . Opening_Hours.close_hour;
        }
        elseif (date("His") < Opening_Hours.open_hour)
        {
            return 'Closed: Next opening hours: ' . convert_numeric_day_to_full_representation($current_day) . ' ' . Opening_Hours.open_hour . ' – ' . Opening_Hours.close_hour;
        }
        else
        {
            return check_hours($tomorrow, '000000');
        }
    }
    else
    {
        return check_hours($tomorrow, '000000');
    }
}

Upvotes: 1

Tim Lytle
Tim Lytle

Reputation: 17624

In the past, I've handled this by using a time stamp without a date (seconds since midnight). So for Saturday, the open would be 62100 and the close would be 91800.

My thought was this removes some of the logic needed when a close crosses midnight, as you only need to compare the seconds since the start of the date to the time range.

And it's pretty easy to check if it's still open from 'yesterday' - just add 86400 to the current 'time' (seconds since the start of the day) and check against the previous day.

Probably all a single SQL statement.

Upvotes: 3

Related Questions