Database of Show Times

[bschultz]

I work at a radio station.  I have all of our shows in a database for displaying on the websites.  We have regular scheduled shows...and then we have some specialty shows that air at ransom times.

I have the list of regular shows that gets pulled into the database each night for the next day.  After it pulls the regular shows in, it pulls the list of speciality shows (in a different table) in for the next day.

Here's how it looks:

Regular Show #1 is on 12pm - 3pm
Specialty Show #1 is on 2pm - 5pm
Regular Show #3 is on 3pm - 6pm

I need it to read:

Regular Show #1 is on 12pm - 2pm
Specialty Show #1 is on 2pm - 5pm
Regular Show #3 is on 5pm - 6pm

All of the fields are datetime.  I'm sure I could put all the start and end times into two arrays, compare the two to see if a start time is BEFORE the PREVIOUS shows endtime.  If I did that, I'm having a hard time wrapping my head around the logic to back up one spot in the array, change the end time to be the same as the start time of the specialty show, and then find the start time of the next regular show...and change it to the end time of the specialty show.

Is there an better way to do this?

If not...how would I manage the logic of stepping thru the arrays?

Thanks!

[requinix]

Why is the current version so badly incorrect with the end times? That shouldn't be something hard to get right if the times are in the database. Are you saying the end times are not in there? What's your code and what data are you working with?

[bschultz]

The end times ARE in the database...but for the regular shows NORMAL end time.  If there is a special show on that particular day, the start or end times will vary from the NORMAL time that's in the database.

I haven't written any code...since I don't even know where I should start manipulating the times

[mac_gyver]

the logic i would try would be -

 

retrieve the regular show times into one array. retrieve the special show times into a second array.

 

if the special show times array is empty, do nothing.

 

loop over the regular show times array. for each regular show entry, loop over the array of special show times. if a special show time start is between the regular show time start and end, change the end to be the special show time start. if a special show time end is between the regular show time start and end, change the start to be the special show time end. this will take two nested foreach(){} loops with two conditional statements inside the inner most loop.

 

merge the resultant regular and the special show time arrays and sort by the start time.

[mac_gyver]

there are some special cases for the logic that need to be addressed.

 

special shows can cause a regular show to end up with zero length, i.e. its start and end times are the same. these would either need to be filtered out or displayed with a notation that they are superseded by special show(s).

 

if a regular show is exactly the same as or contained within the span of a special show, it should have its end time set to its start time. this will result in a zero length regular show and it would be filtered out or displayed as mentioned above.

[bschultz]

Finally had some time to work on this...I'm getting an undefined offset error.  Am I even going in the right direction?  Print_r shows me the two results...so the offset should work.

$regular_starts = array("Regular Show #1" => '2018-04-02 12:00:00', "Regular Show #2" => '2018-04-02 15:00:00');
$regular_ends = array("Regular Show #1" => '2018-04-02 15:00:00', "Regular Show #2" => '2018-04-02 18:00:00');
print_r($regular_starts);

$special_starts = array("Special Show #1" => '2018-04-02 14:00:00');
$special_ends = array("Special Show #1" => '2018-04-02 17:00:00');


$r = 0; // regular
$s = 0; // special

$how_many_shows = count($regular_starts);

while ($r <= $how_many_shows)

{
foreach ($regular_ends as $regular)
  {
foreach ($special_starts as $special)
     {
                                     
  if ($special <= $regular) { echo "Show " . $special_starts[$s] . "starts BEFORE the end of the " . $regular_starts[$r] . "...a change needs to be made\n"; }
$s++;
     }
$r++;
  }

} // end while

[bschultz]

OK...so I read up on multidimentional arrays.  I'd say this is headed in the right direction...but how do I work the logic for if special starts BEFORE the PREVIOUS regular?

 

$regular = array(
  array(
    "title" => "Regular #1",
    "start" => "2018-04-02 12:00:00",
    "end" => "2018-04-02 15:00:00"
  ),
  array(
    "title" => "Regular #2",
    "start" => "2018-04-02 15:00:00",
    "end" => "2018-04-02 18:00:00"
  ),
);

$specialty = array(
  array(
    "title" => "Special #1",
    "start" => "2018-04-02 18:00:00",
    "end" => "2018-04-02 19:00:00"
  ),
);

foreach ( $regular as $show ) {  
  foreach ( $specialty as $special ) {

if (strtotime($special['start']) > strtotime($show['start']) && strtotime($special['start']) < strtotime($show['end'])) { echo "$special[title] falls between start end end of $show[title]\n";  }

} // end regular as show

} // end specialty as special

Edited April 4, 2018 by bschultz

[bschultz]

The more I think of this...that logic won't work either.  What if a special show runs for 6 hours?  That would never fall between start and end of any one show.  That would fall somewhere between Show #1 and Show #3

Not going to lie...this is far and away above anything I've ever written before.

[Barand]

This was a problem that piqued my interest. I started out comparing start and end times, looking for overlaps and so on. I was doing quite well with my test scenario until I got to the final regular show that was interrupted twice by special shows. It was there my algorithm collapsed in a heap.

 

At tat point I embarked on a brute force solution, splitting the day into timeslots (15 minute slots to accomodate my data times). These slots were then filled with priority special shows and remaining empty slots filled by the regualr schedule.

 

Data and Results

 

mysql> select * from regular_show;                                   mysql> select * from special_show;
+--------+-----------+---------------------+---------------------+   +--------+-----------+---------------------+---------------------+
| idshow | title     | start_time          | end_time            |   | idshow | title     | start_time          | end_time            |
+--------+-----------+---------------------+---------------------+   +--------+-----------+---------------------+---------------------+
|      1 | Regular 1 | 2018-04-03 09:00:00 | 2018-04-03 11:00:00 |   |      1 | Special 1 | 2018-04-03 08:00:00 | 2018-04-03 10:00:00 |
|      2 | Regular 2 | 2018-04-03 11:00:00 | 2018-04-03 12:00:00 |   |      2 | Special 2 | 2018-04-03 11:00:00 | 2018-04-03 12:00:00 |
|      3 | Regular 3 | 2018-04-03 12:00:00 | 2018-04-03 15:00:00 |   |      3 | Special 3 | 2018-04-03 14:00:00 | 2018-04-03 16:00:00 |
|      4 | Regular 4 | 2018-04-03 15:00:00 | 2018-04-03 16:00:00 |   |      4 | Special 4 | 2018-04-03 19:00:00 | 2018-04-03 20:00:00 |
|      5 | Regular 5 | 2018-04-03 16:00:00 | 2018-04-03 18:00:00 |   |      5 | Special 5 | 2018-04-03 20:30:00 | 2018-04-03 21:00:00 |
|      6 | Regular 6 | 2018-04-03 18:00:00 | 2018-04-03 20:00:00 |   |      6 | Special 6 | 2018-04-03 21:15:00 | 2018-04-03 21:30:00 |
|      7 | Regular 7 | 2018-04-03 20:00:00 | 2018-04-03 23:00:00 |   +--------+-----------+---------------------+---------------------+
+--------+-----------+---------------------+---------------------+

** RESULTS
 
        Special 1                8:00am to 10:00am   
        Regular 1               10:00am to 11:00am   
        Special 2               11:00am to 12:00pm   
        Regular 3               12:00pm to 2:00pm    
        Special 3                2:00pm to 4:00pm    
        Regular 5                4:00pm to 6:00pm    
        Regular 6                6:00pm to 7:00pm    
        Special 4                7:00pm to 8:00pm    
        Regular 7                8:00pm to 8:30pm    
        Special 5                8:30pm to 9:00pm    
        Regular 7                9:00pm to 9:15pm    
        Special 6                9:15pm to 9:30pm    
        Regular 7                9:30pm to 11:00pm  
 

 

My code

 

function publish($show, $start, $end)
{
    $s = (new DateTime($start))->format('g:ia');
    $e = (new DateTime($end))->modify('+15 minutes')->format('g:ia');
    printf('%-20s %10s to %-10s<br>', $show, $s, $e );
}

// ***************************************
// Shortest interval used is 15 min,
// so create array of 15 min
// timeslots for the day
// ***************************************
$schedule_date = '2018-04-03';

$dt1 = new DateTime($schedule_date.'07:00');
$dt2 = new DateTime($schedule_date.'23:59');
$di =  new DateInterval('PT15M');
$dp =  new DatePeriod($dt1, $di, $dt2);

$timeslots = [];
foreach ($dp as $t) {
    $time = $t->format('H:i');
    $timeslots[$time] = '';
}
// *******************************************
// get al1 shows, placing special shows first
// to give them priority placement
// *******************************************
$res = $db->prepare("SELECT *
                    FROM
                        (
                        SELECT r.title
                             , r.start_time
                             , r.end_time
                             , 2 as priority
                        FROM regular_show r
                        UNION
                        SELECT s.title
                             , s.start_time
                             , s.end_time
                             , 1 as priority
                        FROM special_show s
                        ORDER BY  priority, start_time, end_time
                        ) shows
                    WHERE DATE(start_time) = ?");
$res->execute( [$schedule_date] );
// ******************************************************
// place shows in their respective timeslots
// only placing shows if the timeslot
// is still empty
// ******************************************************
foreach ($res as $r) {
    $st = date('H:i', strtotime($r['start_time']));
    $et = date('H:i', strtotime($r['end_time']));
    foreach ($timeslots as $hm => $v) {
        if ($hm >= $st && $hm < $et) {
            if (empty($v)) {
                $timeslots[$hm] = $r['title'];
            }
        }
    }
}
// ****************************************************
// find the start and end of each show and
// publish the times
// ****************************************************
$schedule = [];
$prev = '';
foreach ($timeslots as $hm => $show) {
    if (!$show) continue;
    if ($show != $prev) {
        $title = $show;
        $st = $hm;
        $prev = $show;
    }
    $schedule[$st] = [ $title, $hm ];
}

foreach ($schedule as $st => $show) {
    publish($show[0], $st, $show[1]);
}

[bschultz]

Barand,

Many thanks...I would NEVER come up with that.  Very much appreciated!