CODEWITHSUNDEEP
phpmysqldatabasedatabase-design
I'll-Be-Back
I'll-Be-Back

Reputation: 10828

Handling Shops Open and Close time

What is the best way to store shop opening and closing time in the database and also how to calculate the time in PHP?

I have come up with this table design:

+----+---------+----------+-----------+------------+
| id | shop_id | week_day | open_hour | close_hour |
+----+---------+----------+-----------+------------+
|  1 |       3 |        1 | 15:00:00  | 23:00:00   |
|  2 |       3 |        2 | 15:00:00  | 23:00:00   |
|  3 |       3 |        3 | 18:00:00  | 02:00:00   |
|  4 |       3 |        4 | 18:00:00  | 02:00:00   |
|  5 |       3 |        5 | 18:00:00  | 03:00:00   |
+----+---------+----------+-----------+------------+

+------------+---------+------+-----+---------+----------------+
| Field      | Type    | Null | Key | Default | Extra          |
+------------+---------+------+-----+---------+----------------+
| id         | int(11) | NO   | PRI | NULL    | auto_increment |
| shop_id    | int(11) | NO   |     | NULL    |                |
| week_day   | int(11) | NO   |     | NULL    |                |
| open_hour  | time    | NO   |     | NULL    |                |
| close_hour | time    | NO   |     | NULL    |                |
+------------+---------+------+-----+---------+----------------+

For example, on Tuesday (week_day = 2) it open at 3PM and close at 11PM (Tuesday).

On Wednesday (`week_day = 2'), it open at 6PM and close after midnight at 2AM which would be Thursday. Should after midnight time saved in same row?

Let say customer want to place an order (shop_id = 3) at 10PM on Tuesday, they should be able to do so according to the database data. However if customer want to place an order at 1AM on Thursday but the database show that week_day = 3 it close at 02:00:00

How to write in PHP to work out if the shop open or not?

Do I need to change the the table design so it would much easier to write in PHP?

Upvotes: 0

Views: 362

Answers (2)

skajfes
skajfes

Reputation: 8265

You can say that if close_hour is less than open_hour for a given entry that the opening hour is in the next day.

Or you could use separate week_day columns for opening and closing time.

Upvotes: 0

Jake N
Jake N

Reputation: 10583

Try this http://php.net/manual/en/book.datetime.php

with this http://php.net/manual/en/class.dateinterval.php

Upvotes: 1

Related Questions