打印餐厅营业时间从数据库表以人类可读的格式使用PHP [英] printing restaurant opening hours from a database table in human readable format using php

查看：169
本文介绍了打印餐厅营业时间从数据库表以人类可读的格式使用PHP的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我有一个表格列出了餐厅的开放时间。列为id，eateries_id，day_of_week，start_time和end_time。每个餐厅在表中多次表示，因为每天有一个单独的条目。有关详细信息，请参阅此前面的问题：
使用数据库，php，js确定餐厅现在是否打开（如yelp）



如何从此表中获取数据，并以人类可读的格式打印出来。例如，代替说M 1-3，T 1-3，W 1-3，Th 1-3，F 1-8，我想说M-Th 1-3，F 1-8 。类似地，我想要M 1-3,5-8而不是M 1-3，M 5-8。

$ p

解决方案

以为我会有一个bash。



测试表

  CREATE TABLE`open_hours`（
`id` int（11）NOT NULL AUTO_INCREMENT，
`eateries_id` int（11）DEFAULT NULL，
`day_of_week` int （11）DEFAULT NULL，
`start_time` time DEFAULT NULL，
`end_time` time DEFAULT NULL，
 PRIMARY KEY（`id`）
）

测试数据

  INTO`test`.`opening_hours` 
（
`eateries_id`，
`day_of_week`，
`start_time`，
`end_time`）
 SELECT 2 AS eateries_id，1 AS day_of_week，'13：00'AS start_time，'15：00'as end_time union 
 SELECT 2 AS eateries_id，1 AS day_of_week，'17：00'AS start_time，'20：00 'as end_time union 
 SELECT 2 AS eateries_id，2 AS day_of_week，'13：00'AS start_time，'15：00'as end_time union 
 SELECT 2 AS eateries_id，2 AS day_of_week，'17 ：00'AS start_time，'20：00'as end_time union all 
 SELECT 2 AS eateries_id，3 AS day_of_week，'13：00'AS start_time，'15：00'as end_time union 
 SELECT 2 AS eateries_id，4 AS day_of_week，'13：00'AS start_time，'20：00'as end_time union 
 SELECT 2 AS eateries_id，5 AS day_of_week，'13：00'AS start_time，'15：00 'as end_time union 
 SELECT 2 AS eateries_id，6 AS day_of_week，'13：00'AS start_time，'20：00'as end_time union 
 SELECT 2 AS eateries_id 7 AS day_of_week，'13 ：00'AS start_time，'21：00'as end_time 
 union all 
 SELECT 3 AS eateries_id，1 AS day_of_week，'13：00'AS start_time，'15：00'as end_time union all b $ b SELECT 3 AS eateries_id，2 AS day_of_week，'13：00'AS start_time，'15：00'as end_time union 
 SELECT 3 AS eateries_id，3 AS day_of_week，'13：00'AS start_time， '15：00'as end_time union all 
 SELECT 3 AS eateries_id，4 AS day_of_week，'13：00'AS start_time，'20：00'as end_time union 
 SELECT 3 AS eateries_id，5 AS day_of_week，'13：00'AS start_time，'15：00'as end_time union all 
 SELECT 3 AS eateries_id，6 AS day_of_week，'13：00'AS start_time，'20：00'as end_time union all b $ b SELECT 3 AS eateries_id，7 AS day_of_week，'13：00'AS start_time，'21：00'as end_time

查看定义以按日合并开放时间

  CREATE VIEW`test`.`groupedhours` 
 AS 
 select`test`.`opening_hours`.`eateries_id` AS`eateries_id`，
`test`.`opening_hours`.`day_of_week` AS`day_of_week`，
 group_concat （date_format（`test`.`open_hours`.``start_time`，'％l'），' - '，date_format（`test`.`opening_hours`.`end_time`，'％l％p' test`.`pening_hours`.`start_time` ASC separator'，'）AS`OpeningHours` 
从`test`.`opening_hours` 
 group by`test`.`opening_hours`.`eateries_id`， `test`.`opening_hours`.`day_of_week`

查询连续日期的岛屿 

  SET @rownum = NULL; 
 SET @ rownum2 = NULL; 
 
 
 
 SELECT S.eateries_id，
 concat（CASE WHEN 
 S.day_of_week<>> E.day_of_week 
 THEN 
 CONCAT（CASE S.day_of_week 
 WHEN 1 THEN'Su'
 WHEN 2 THEN'Mo'
 WHEN 3 THEN'Tu'
 WHEN 4 THEN'We'
 WHEN 5 THEN'Th'
 WHEN 6 THEN'Fr'
 WHEN 7 THEN'Sa'
 End，' - '）
 ELSE''
 END ，
 CASE E.day_of_week 
 WHEN 1 THEN'Su'
 WHEN 2 THEN'Mo'
 WHEN 3 THEN'Tu'
 WHEN 4 THEN'We'
 WHEN 5 THEN'Th'
 WHEN 6 THEN'Fr'
 WHEN 7 THEN'Sa'
 End，''，S.OpeningHours）AS`Range` 
 FROM（
 
 SELECT 
 A.day_of_week，
 @rownum：= IFNULL（@rownum，0）+ 1 AS rownum，
 A.eateries_id，
 A.OpeningHours 
 FROM`test`.`groupedhours` as A 
 WHERE NOT EXISTS（SELECT * FROM`test`.`groupedhours` B 
 WHERE A.eateries_id = B.eateries_id 
 AND A.OpeningHours = B.OpeningHours 
 AND B.day_of_week = A.day_of_week -1）
 ORDER BY eateries_id，day_of_week）AS S 
 
 JOIN（
 SELECT 
 A.day_of_week，
 @ rownum2：= IFNULL（@ rownum2，0）+ 1 AS rownum，
 A.eateries_id，
 A.OpeningHours 
 FROM` test`.`groupedhours` as A 
 WHERE NOT EXISTS（SELECT * FROM`test`.`groupedhours` B 
 WHERE A.eateries_id = B.eateries_id 
 AND A.OpeningHours = B.OpeningHours 
 AND B.day_of_week = A.day_of_week + 1）
 ORDER BY eateries_id，day_of_week）AS E 
 
 ON S.eateries_id = E.eateries_id AND 
 S.OpeningHours = S.OpeningHours AND 
 S.rownum = E.rownum 
  

  eateries_id范围
 2 Su  -  Mo 1  -  3 PM，5  -  8 PM 
 2 Tu 1 -  3 PM 
 2我们1  -  8 PM 
 2 Th 1  -  3 PM 
 2 Fr 1  -  8 PM 
 2 Sa 1  -  9 PM 
 3 Su  - Tu 1  -  3 PM 
 3我们1  -  8 PM 
 3 Th 1  -  3 PM 
 3 Fr 1  -  8 PM 
 3 Sa 1  -  9 PM


i have a table that lists the opening hours of restaurants. the columns are id, eateries_id, day_of_week, start_time, and end_time. each eatery is represented in the table multiple times because there is a separate entry for each day. see this previous question for more details: determine if a restaurant is open now (like yelp does) using database, php, js

i'm wondering now how to take the data from this table and print it out in a human readable format. for example, instead of saying "M 1-3, T 1-3, W 1-3, Th 1-3, F 1-8" i would like to say "M-Th 1-3, F 1-8". similarly, i want "M 1-3, 5-8" instead of "M 1-3, M 5-8". how might i do this without a brute force method of numerous if statements?

thanks.

解决方案

Thought I would have a bash at this.

Test Table

CREATE TABLE `opening_hours` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `eateries_id` int(11) DEFAULT NULL,
  `day_of_week` int(11) DEFAULT NULL,
  `start_time` time DEFAULT NULL,
  `end_time` time DEFAULT NULL,
  PRIMARY KEY (`id`)
)

Test Data

INSERT INTO `test`.`opening_hours`
(
`eateries_id`,
`day_of_week`,
`start_time`,
`end_time`)
SELECT 2 AS eateries_id, 1 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all
SELECT 2 AS eateries_id, 1 AS day_of_week, '17:00' AS start_time, '20:00' as end_time union all
SELECT 2 AS eateries_id, 2 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all
SELECT 2 AS eateries_id, 2 AS day_of_week, '17:00' AS start_time, '20:00' as end_time union all
SELECT 2 AS eateries_id, 3 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all
SELECT 2 AS eateries_id, 4 AS day_of_week, '13:00' AS start_time, '20:00' as end_time union all
SELECT 2 AS eateries_id, 5 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all
SELECT 2 AS eateries_id, 6 AS day_of_week, '13:00' AS start_time, '20:00' as end_time union all
SELECT 2 AS eateries_id, 7 AS day_of_week, '13:00' AS start_time, '21:00' as end_time
                                                                       union all
SELECT 3 AS eateries_id, 1 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all
SELECT 3 AS eateries_id, 2 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all
SELECT 3 AS eateries_id, 3 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all
SELECT 3 AS eateries_id, 4 AS day_of_week, '13:00' AS start_time, '20:00' as end_time union all
SELECT 3 AS eateries_id, 5 AS day_of_week, '13:00' AS start_time, '15:00' as end_time union all
SELECT 3 AS eateries_id, 6 AS day_of_week, '13:00' AS start_time, '20:00' as end_time union all
SELECT 3 AS eateries_id, 7 AS day_of_week, '13:00' AS start_time, '21:00'  as end_time

View definition to consolidate opening hours by day

CREATE VIEW `test`.`groupedhours` 
AS 
  select `test`.`opening_hours`.`eateries_id` AS `eateries_id`,
         `test`.`opening_hours`.`day_of_week` AS `day_of_week`,
         group_concat(concat(date_format(`test`.`opening_hours`.`start_time`,'%l'),' - ',date_format(`test`.`opening_hours`.`end_time`,'%l %p')) order by `test`.`opening_hours`.`start_time` ASC separator ', ') AS `OpeningHours` 
         from `test`.`opening_hours` 
         group by `test`.`opening_hours`.`eateries_id`,`test`.`opening_hours`.`day_of_week`

Query to find the 'islands' of contiguous days with the same opening hours (based on one by Itzik Ben Gan)

SET @rownum = NULL;
SET @rownum2 = NULL;



SELECT S.eateries_id, 
concat(CASE WHEN 
S.day_of_week <> E.day_of_week 
    THEN 
    CONCAT(CASE S.day_of_week 
             WHEN 1 THEN 'Su'
             WHEN 2 THEN 'Mo'     
             WHEN 3 THEN 'Tu'     
             WHEN 4 THEN 'We'
             WHEN 5 THEN 'Th'    
             WHEN 6 THEN 'Fr'    
             WHEN 7 THEN 'Sa'  
            End, ' - ')
    ELSE ''        
END,
CASE E.day_of_week 
     WHEN 1 THEN 'Su'
     WHEN 2 THEN 'Mo'     
     WHEN 3 THEN 'Tu'     
     WHEN 4 THEN 'We'
     WHEN 5 THEN 'Th'    
     WHEN 6 THEN 'Fr'    
     WHEN 7 THEN 'Sa'  
End, ' ', S.OpeningHours) AS `Range`
FROM (

SELECT 
    A.day_of_week,
    @rownum := IFNULL(@rownum, 0) + 1  AS rownum,
    A.eateries_id,
    A.OpeningHours
FROM `test`.`groupedhours` as A
WHERE NOT EXISTS(SELECT * FROM `test`.`groupedhours` B
                 WHERE A.eateries_id = B.eateries_id
                  AND A.OpeningHours = B.OpeningHours
                  AND B.day_of_week = A.day_of_week -1) 
ORDER BY eateries_id,day_of_week) AS S

JOIN (
SELECT 
    A.day_of_week,
    @rownum2 := IFNULL(@rownum2, 0) + 1  AS rownum,
    A.eateries_id,
    A.OpeningHours
FROM `test`.`groupedhours` as A 
WHERE NOT EXISTS(SELECT * FROM `test`.`groupedhours` B
                 WHERE A.eateries_id = B.eateries_id
                  AND A.OpeningHours = B.OpeningHours
                  AND B.day_of_week = A.day_of_week + 1)
ORDER BY eateries_id,day_of_week) AS E

ON  S.eateries_id = E.eateries_id AND
    S.OpeningHours = S.OpeningHours AND 
    S.rownum = E.rownum

Results

eateries_id             Range
2                Su - Mo 1 - 3 PM, 5 - 8 PM
2                Tu 1 - 3 PM
2                We 1 - 8 PM
2                Th 1 - 3 PM
2                Fr 1 - 8 PM
2                Sa 1 - 9 PM
3                Su - Tu 1 - 3 PM
3                We 1 - 8 PM
3                Th 1 - 3 PM
3                Fr 1 - 8 PM
3                Sa 1 - 9 PM

这篇关于打印餐厅营业时间从数据库表以人类可读的格式使用PHP的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
PHP最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆