存储/显示重复的每周时间表的方法

I would like to store and retrieve a dynamic weekly schedule that is not at all dependent on the actual date.

我想存储和检索一个完全不依赖于实际日期的动态每周计划。

The data would be stored in a MySQL table like this (not ordered by time):

数据将存储在这样的MySQL表中(不按时间排序):

(Class and Instructor columns will store reference IDs of other tables but I've used actual names here to make it easier to read at a glance)

(Class和Instructor列将存储其他表的引用ID,但我在这里使用实际名称以便于一目了然)

----------------------------------------------------------------------
|    id    |    time    | dayofweek |    class    |    instructor    |
----------------------------------------------------------------------
|    1     |   6:30a    |     1     |    Zumba    |    Julie         |
----------------------------------------------------------------------
|    2     |   9:00a    |     3     |   Kickbox   |    Devon         |
----------------------------------------------------------------------
|    3     |   11:00a   |     4     |    Zumba    |    Alex          |
----------------------------------------------------------------------
|    4     |   6:30a    |     4     |    Dance    |    Karen         |
----------------------------------------------------------------------
|    5     |   5:00p    |     1     |    R-BAR    |    Karen         |
----------------------------------------------------------------------
|    6     |   5:00p    |     6     |    Dance    |    Karen         |
----------------------------------------------------------------------
|    7     |   9:00a    |     7     |    Kinder   |    Julie         |

The final output would visually look something like this (ordered by time):

最终输出在视觉上看起来像这样(按时间排序):

          ---------------------------------------------------------
          |  Sun  |  Mon  |  Tue  |  Wed  |  Thu  |  Fri  |  Sat  |
-------------------------------------------------------------------
|  6:30a  | Zumba |       |       | Dance |       |       |       |
-------------------------------------------------------------------
|  9:00a  |       |       |Kickbox|       |       |       |Kinder |
-------------------------------------------------------------------
|  11:30a |       |       |       | Zumba |       |       |       |
-------------------------------------------------------------------
|  5:00p  | R-BAR |       |       |       |       | Dance |       |
-------------------------------------------------------------------

But I can't wrap my head around how to accomplish this efficiently. I've searched Google for hours today and have come across a few posts that look like they might work but it's never quite what I'm looking for.

但我不能完全理解如何有效地实现这一目标。我今天在谷歌搜索了几个小时,发现了一些看起来可能有用的帖子,但它们从来都不是我想要的。

I started out thinking about running a separate query for each of the 7 days per time slot, through a function or otherwise, but that's seriously sloppy and way too many queries for such a simple task. Obviously all 7 days (columns) will always show but timeslots (rows) may be added or removed anytime depending if there is an event at that time.

我开始考虑通过函数或其他方式为每个时间段的7天中的每一天运行单独的查询,但这对于这样一个简单的任务来说是非常草率和太多的查询。显然,所有7天(列)将始终显示,但可以随时添加或删除时隙(行),具体取决于当时是否有事件。

Next I looked into storing everything in an array and combining all rows with duplicate times then process their days one by one. I'm not sure how I would do that dynamically though...

接下来,我研究了将所有内容存储在一个数组中,并将所有行重复一次,然后逐个处理它们的日期。我不确定如何动态地做到这一点......

I found this example and I think it is pretty close to what I need: PHP - Merge duplicate array keys in a multidimensional array

我找到了这个例子,我认为它非常接近我需要的东西:PHP - 在多维数组中合并重复的数组键

After all is said and done I am planning on making a simple admin page for the user to add or remove events. Any ideas?

毕竟说完了,我打算为用户创建一个简单的管理页面来添加或删除事件。有任何想法吗?

2 个解决方案

#1

$a=array();

$a[] = array( 'id'=>'1' ,'time'=>'6:30a'   , 'dayofweek'=>'2' ,    'class'=>'Zumba');
$a[] = array( 'id'=>'2' ,'time'=>'6:40a'   , 'dayofweek'=>'3' ,    'class'=>'Zumba');
$a[] = array( 'id'=>'2' ,'time'=>'6:20a'   , 'dayofweek'=>'3' ,    'class'=>'Zumba');
$a[] = array( 'id'=>'2' ,'time'=>'1:20p'   , 'dayofweek'=>'3' ,    'class'=>'Zumba');

$new_array=array();
foreach($a AS $k =>$v){
     if(!array_key_exists($v['time'],$new_array)){
          $new_array[$v['time']]=array("","","","","","","","");
          unset($new_array[$v['time']][0]);
     }

    $new_array[$v['time']][$v['dayofweek']]=$v['class'];

}

function cmp($a, $b)
{

    $a = preg_replace('{\:}', '', $a);
    $a = preg_replace('{a}', '', $a);
    $a = preg_replace('{(.*?)p}', '100$1', $a);
    $a = (int)$a;
    $b = preg_replace('{\:}', '', $b);
    $b = preg_replace('{a}', '', $b);
    $b = preg_replace('{(.*?)p}', '100$1', $b);
    $b = (int)$b;

    if ($a == $b) {
        return 0;
    }
    return ($a < $b) ? -1 : 1;  
}


uksort($new_array, "cmp");
$weekmap = array( '','Sun','Mon','Tue','Wed','Thu','Fri','Sat');
print_r($new_array);



foreach($new_array AS $k =>$v){
echo $k.'::';
        foreach($v AS $k1 =>$v1){
            //echo $weekmap[$k1];
            //echo '->';
            if($v1==''){
            echo 'null';
            }
            echo $v1;
            echo '|';
        }
         echo PHP_EOL;
}

output

Array
(
    [6:20a] => Array
        (
            [1] => 
            [2] => 
            [3] => Zumba
            [4] => 
            [5] => 
            [6] => 
            [7] => 
        )

    [6:30a] => Array
        (
            [1] => 
            [2] => Zumba
            [3] => 
            [4] => 
            [5] => 
            [6] => 
            [7] => 
        )

    [6:40a] => Array
        (
            [1] => 
            [2] => 
            [3] => Zumba
            [4] => 
            [5] => 
            [6] => 
            [7] => 
        )

    [1:20p] => Array
        (
            [1] => 
            [2] => 
            [3] => Zumba
            [4] => 
            [5] => 
            [6] => 
            [7] => 
        )

)
6:20a::null|null|Zumba|null|null|null|null|
6:30a::null|Zumba|null|null|null|null|null|
6:40a::null|null|Zumba|null|null|null|null|
1:20p::null|null|Zumba|null|null|null|null|

http://sandbox.onlinephpfunctions.com/code/8da03b1833f58e7f60888cfcfb6e544cd3ff10ad

#2

I'll suggest following approach:

我会建议以下方法:

1. SELECT DISTINCT time FROM table;

   SELECT DISTINCT time FROM table;

2. SELECT DISTINCT dayofweek FROM table;

   SELECT DISTINCT dayofweek FROM table;

3. SELECT * FROM table;

   SELECT * FROM table;

4. Build columns with day of week. (2nd query result)

   使用星期几构建列。 (第二查询结果)

5. Build rows with time of action. (1st query result)

根据行动时间构建行。 (第1次查询结果)

6. For each cell of table (exclude 1st row and 1st column) use

   对于表的每个单元格(不包括第1行和第1列)使用

   foreach($result /* of 3rd query */ as $row){
        if(($row['time'] == $celltime) && ($row['day'] == $cellday)){
             // show formatted row
             // remove row from result buffer, so it should never appear again
             // also row removement would increase speed for further search
         } else {
             // ignore or something
         }
   }
   

Good thing is to build each cell id based on time and dayofweek, like 'cell-9:00a-3', or 'cell-11:00a-4' (with id.split('-').slice(1)), so you may extract this data of cell on a runtime with javascript / or submit through ajax further.

好的方法是根据时间和日期来构建每个单元格id,例如'cell-9:00a-3',或'cell-11:00a-4'(带有id.split(' - ')。slice(1) ),因此您可以使用javascript /在运行时提取此单元格数据,或者通过ajax进一步提交。

---

Eather, I suggest to normalize your table, splitting it into 3-4 or more (if needed):

Eather,我建议将你的桌子标准化,将其分成3-4个或更多(如果需要):

Where UN = Unsigned, NN = Not Null, AI = AUTO_INCREMENT.

UN =无符号时,NN =非空,AI = AUTO_INCREMENT。

Benefits:

1. You have more fast access to DISTINCT data columns, as they're separated.

您可以更快速地访问DISTINCT数据列,因为它们是分开的。

2. You may easier access to values via cell id, like 'cell-1-2-3-4', with

   您可以通过单元格ID更轻松地访问值,例如“cell-1-2-3-4”

   list(, $timeId, $dayId, $classId, $instructorId) = explode('-', $_POST['cell']);
   

   If, you'll post where or what you want to modify.

   如果,您将发布您想要修改的位置或内容。

3. Less repeats of VARCHAR() or string data.

VARCHAR()或字符串数​​据的重复次数较少。

4. This also solves time-slot removement problem, because might be configured with ON CASCADE DELETE for FK_Schedule_Time.

这也解决了时隙删除问题,因为可能为FK_Schedule_Time配置了ON CASCADE DELETE。

etc...

---

UPDv1:

Well, lets try to visualize what I was meaning:

好吧,让我们试着想象一下我的意思:

I mean, that to display schedule (or any other pivot table), you should obtain distinct values of label rows / columns, i.e. day names / numbers or time. Then build a table, which contains thouse distinct values, as X / Y of plot. Then, seek for [X:Y] intersections (like points on graph) - they would be pivoted data.

我的意思是,要显示计划(或任何其他数据透视表),您应该获得标签行/列的不同值,即日期名称/数字或时间。然后构建一个表,其中包含不同的值,作为绘图的X / Y.然后,寻找[X:Y]交叉点(如图上的点) - 它们将是数据透视。

The one schedule I wrote once has taken 3 months of my life. I will not say, that it is perfect now, but it works. Try to simplify your task: split to smaller ones. Then you will see a bigger picture.

我写过的一个时间表花了我3个月的时间。我不会说,它现在是完美的,但它确实有效。尝试简化您的任务:拆分为较小的任务。然后你会看到更大的画面。