存储/显示重复每周计划的方法 [英] Method for storing/displaying repeating weekly schedule
问题描述
我想存储和检索一个不依赖于实际日期的动态每周计划。
数据将存储在这样的MySQL表中（不按时间排序）：
和教师列将存储其他表的引用ID，但我在这里使用实际名称，使其更容易阅读一目了然）
------------------------------------------------ ----------------------
| id |时间| dayofweek |类|教师|
---------------------------------------------- ------------------------
| 1 | 6：30a | 1 | Zumba | Julie |
---------------------------------------------- ------------------------
| 2 | 9：00a | 3 | Kickbox | Devon |
---------------------------------------------- ------------------------
| 3 | 11：00a | 4 | zumba | Alex |
---------------------------------------------- ------------------------
| 4 | 6：30a | 4 |舞蹈|凯伦|
---------------------------------------------- ------------------------
| 5 | 5：00p | 1 | R-BAR |凯伦|
---------------------------------------------- ------------------------
| 6 | 5：00p | 6 |舞蹈|凯伦|
---------------------------------------------- ------------------------
| 7 | 9：00a | 7 | Kinder | Julie |
最终输出将看起来像这样（按时间排序）：
-------------------------------- -------------------------
| Sun |星期一| Tue |周三| Thu |周五| Sat |
---------------------------------------------- ---------------------
| 6：30a | Zumba | | |舞蹈| | | |
---------------------------------- ---------------------
| 9：00a | | | Kickbox | | | | Kinder |
---------------------------------------------- ---------------------
| 11：30a | | | | zumba | | | |
---------------------------------------------- ---------------------
| 5：00p | R-BAR | | | | |舞蹈| |
---------------------------------------------- ---------------------
但我不能包装我的头如何有效地完成这一切。我已经在Google上搜索了几个小时，并发现了一些看起来像他们可能工作，但它从来不是我想要的东西。
我开始了考虑通过一个函数或其他方式对每个时隙7天的每一个运行一个单独的查询，但是这是非常草率和太多查询这样一个简单的任务。显然，根据当时是否有活动，所有7天（栏）总是会显示，但时间槽（列）可能会随时新增或移除。 将所有数据存储在数组中，并将所有行与重复的时间组合，然后逐个处理它们的日期。我不知道我将如何动态，虽然...
我发现这个例子，我认为这是非常接近我需要：
PHP - 在多维数组中合并重复的数组键
毕竟，我打算做一个简单的管理页面让用户添加或删除事件。任何想法？
我会建议以下方法：
-
SELECT DISTINCT time FROM table; -
SELECT DISTINCT dayofweek FROM table; -
SELECT * FROM table; -
使用星期几建立栏。 （第2个查询结果）
- 使用操作时间创建行。 （第1个查询结果）
-
对于表格的每个单元格（排除第1行和第1列），请使用
foreach（$ result / * of 3rd query * / as $ row）{
if（（$ row ['time'] == $ celltime）& 'day'] == $ cellday））{
//显示格式化的行
//从结果缓冲区中删除行，所以它不应再出现
//行删除会增加速度进一步搜索
} else {
//忽略或某事
}
}
好的是基于时间和 ，例如'cell-9：00a-3'或'cell-11：00a-4' （与 id.split（' - '）。slice（1）），所以你可以提取这个单元格的数据在运行时javascript
Eather，我建议您将表格标准化，将其拆分为3-4个或更多（如果需要）：
其中 UN =无符号， NN =不Null， AI = AUTO_INCREMENT 。
好处：
- 您可以更快速地访问
DISTINCT数据列。 -
您可以更轻松地通过单元格ID访问值，例如
2-3-4'，与列表（，$ timeId，$ dayId，$ classId ，$ instructorId）= explode（' - '，$ _POST ['cell']）;
如果，您将张贴在哪里或您想要修改的内容。
- 少量重复
VARCHAR（）或字符串数据。 - 这也解决了时隙删除问题，因为可能已为
FK_Schedule_Time配置ON CASCADE DELETE。
等等...
UPDv1：
好吧，让我想象一下我的意思：
我的意思是，要显示计划（或任何其他数据透视表）您应该获得标签行/列的不同值，即日名/数字或时间。然后构建一个表，其中包含不同的值，作为绘图的X / Y。
我写一次的一个日程表已经花了3个月的时间生活。我不会说，它是完美的现在，但它的工作。尝试简化您的任务：拆分为较小的。然后你会看到一个更大的图片。
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):
(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)
----------------------------------------------------------------------
| 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.
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
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?
I'll suggest following approach:
SELECT DISTINCT time FROM table;SELECT DISTINCT dayofweek FROM table;SELECT * FROM table;Build columns with day of week. (2nd query result)
- Build rows with time of action. (1st query result)
For each cell of table (exclude 1st row and 1st column) use
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.
Eather, I suggest to normalize your table, splitting it into 3-4 or more (if needed):
Where UN = Unsigned, NN = Not Null, AI = AUTO_INCREMENT.
Benefits:
- You have more fast access to
DISTINCTdata columns, as they're separated. You may easier access to values via cell id, like
'cell-1-2-3-4', withlist(, $timeId, $dayId, $classId, $instructorId) = explode('-', $_POST['cell']);If, you'll post where or what you want to modify.
- Less repeats of
VARCHAR()or string data. - This also solves time-slot removement problem, because might be configured with
ON CASCADE DELETEforFK_Schedule_Time.
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.
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.
这篇关于存储/显示重复每周计划的方法的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
