Method for storing/displaying repeating weekly schedule

Question

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?

Answer 1

I'll suggest following approach:

1. SELECT DISTINCT time FROM table;

2. SELECT DISTINCT dayofweek FROM table;

3. SELECT * FROM table;

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

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

6. 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:

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

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

   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.
4. This also solves time-slot removement problem, because might be configured with ON CASCADE DELETE for FK_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.

Answer 2

$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