How to group database table data by day and create a calendar with an html table?

Question

I am trying to make a calendar that shows every day of the month and shows the event information for each day. Some days may contain multiple events. The trouble is I'm stuck trying to iterate the result set and group the multiple events into a single day row.

Here's a few sample rows:

Here is my code to retrieve array

$query="SELECT event_date_start, event_date_end, event_description, event_type, swim_club_assigned 
         FROM eventcalendar 
         WHERE event_date_start like '$search_date%'";
$result = mysqli_query($con, $query);

echo "<br>";

// Only loop if there is a result.
if ($result){
    $r = []; // You aren't exactly clear on 'myData'.  In this instance, I am setting the rows inside of it.
    while ($row = $result->fetch_object()) {
        $r[] = $row;
    }
    $result->close(); // Free result set
}

print_r($r); gives me the array below

Array (
    [0] => stdClass Object (
        [event_date_start] => 2018-10-01
        [event_date_end] => 2018-10-01
        [event_description] => Preparation Meet (LC) - Template: A
        [event_type] => GCRSA Club Meet
        [swim_club_assigned] => Gold Coast North SC Inc
    )
    [1] => stdClass Object (
        [event_date_start] => 2018-10-11
        [event_date_end] => 2018-10-11
        [event_description] => Sprint Meet (LC) - Template: H
        [event_type] => GCRSA Club Meet
        [swim_club_assigned] => Bond SC Inc
    )
    [2] => stdClass Object (
        [event_date_start] => 2018-10-31
        [event_date_end] => 2018-10-31
        [event_description] => Reg Winter Championships (SC) - Template: XY
        [event_type] => GCRSA Championship
        [swim_club_assigned] => 
    )
) 

I want to be able to retrieve from the array (where the event type = $something and the start date = $a_date and then display the $swim_club and the $event_description.

while($row = fetch_assoc($r)) {
    if (($row["event_type"] == 'GCRSA Championship') && ($row["event_date_start"] == $day)){
        echo  "{$row['swim_club_assigned']} {$row['event_description']}";
    }
}

I have been trying to figure out how to pull the key data from the array for each row (there may be more than one event per start date in the array), but I can't figure out the syntax to retrieve the row from the array and then pull out the value from the key.

To be clear, it is a calendar I am trying to populate, and the results are to be inserted into a cell in the relevant date row. That's why I thought it best to use one query at the top and to then iterate through an array (as per the example above) at each cell matching the date of an event to the day of the relevant cell. The calendar that I am using is below (with comments to show the cell where I need to insert the data)

<table class="table table-bordered">
    <thead>
         <tr>
            <th rowspan="2" colspan="2">Date</th>
            <th colspan="3">GCRSA Region</th>
            <th colspan="2">State, SAL, Intl</th>
            <th colspan=2><center>Other events</center> </th>
         </tr>
         <tr style="border-bottom: 1pt Darkblue;">
            <th>Club meet</th>
            <th>Championship</th>
            <th>Development</th>
            <th>SQ</th>
            <th>Sal</th>
            <th>School Meets</th>
            <th>Qld School Holidays</th>
          </tr>
    </thead>
    <tbody>
        <?php
        // First day of the month.
        $date_start = date('Y-m-01', strtotime($query_date));
        $day = date('Y-m-01', strtotime($query_date));
        for($i= 1; $i < date('t', strtotime($query_date)) + 1; $i++){
        ?>
            <tr>
            <td <?php if (date('w',strtotime($day)) == 0 || date('w',strtotime($day)) == 6 ){
                echo "style='background-color:red;'";
            }?>><?php echo date('M d, Y',strtotime($day)); 

            ?></td>
            <td <?php if (date('w',strtotime($day)) == 0 || date('w',strtotime($day)) == 6 ){
                echo "style='background-color:red;'";
            }?>><?php echo date('l',strtotime($day)); ?></td>

            <td> <?php //this bit is not working. need to iterate through array result $r to filter appropriate events here 
            while($row = mysqli_fetch_assoc($r)) {
                if  (($row["event_type"] == 'GCRSA Club Meet') && ($row["event_date_start"] == $day)){
                    echo  "{$row['swim_club_assigned']} {$row['event_description']}";
                }
            }
            ?></td>

            <td> <?php //this bit is not working. need to iterate through array result $r to filter appropriate events here 
            while($row = mysqli_fetch_assoc($r)) {
                if  (($row["event_type"] == 'GCRSA Championship') && ($row["event_date_start"] == $day)){
                    echo  "{$row['swim_club_assigned']} {$row['event_description']}";
                }
            }
            ?></td>

        <?php
            for($j =1;$j<=5;$j++){
                echo "<td></td>".PHP_EOL;
            }
        ?>
        </tr>
        <?php 
        $day = date('Y-m-d', strtotime('+1 day', strtotime($day)));
        } 
        ?>                                    
    </tbody>

Answer 1

First, the snippet/file, then the explanation and details...

<style>
.table-bordered {border: solid 1px grey;}
thead tr th {border: solid 1px blue;}
tbody tr td {border: solid 1px green;}
.Sday {background-color: red;}
</style>
<?php
if (!$conn = new mysqli("localhost", "root","","db")) {
    echo "Database Connection Error"; // $conn->connect_error
} else {
    // Generate derived table comprised of united hardcoded dates for the current month with minimal function calls
    $ym = date('Y-m-');
    $derived_table = '';
    for ($d = 1, $end = date('t'); $d <= $end; ++$d) {
        $date = $ym . str_pad($d, 2, 0, STR_PAD_LEFT);  // format the date without calling strtotime() & date() each iteration
        if ($d == 1) {
            $derived_table .= "SELECT '$date' AS event_date_start";  // only need to name the first column
        } else {
            $derived_table .= " UNION SELECT '$date'";
        }
    }
    $query = "SELECT 
                DATE_FORMAT(A.event_date_start, '%b %e, %Y') AS Date,
                DAYNAME(A.event_date_start) AS Day,
                GROUP_CONCAT(
                  IF(event_type = 'GCRSA Club Meet', CONCAT(swim_club_assigned, ' ', event_description), null)
                  SEPARATOR '<br>'
                ) AS `Club Meet`,
                GROUP_CONCAT(
                  IF(event_type = 'GCRSA Championship', CONCAT(swim_club_assigned, ' ', event_description), null)
                  SEPARATOR '<br>'
                ) AS `Championship`
              FROM ($derived_table) A
              LEFT JOIN eventcalendar B ON A.event_date_start = B.event_date_start
              GROUP BY A.event_date_start";
    if (!$result = $conn->query($query)) {
        echo "Syntax Error"; // $conn->error
    } else {
        ?>
        <table class="table table-bordered">
            <thead>
                 <tr>
                    <th rowspan="2" colspan="2">Date</th>
                    <th colspan="3">GCRSA Region</th>
                    <th colspan="2">State, SAL, Intl</th>
                    <th colspan="2">Other events</th>
                 </tr>
                 <tr style="border-bottom: 1pt Darkblue;">
                    <th>Club meet</th>
                    <th>Championship</th>
                    <th>Development</th>
                    <th>SQ</th>
                    <th>Sal</th>
                    <th>School Meets</th>
                    <th>Qld School Holidays</th>
                  </tr>
            </thead>
            <tbody>     
                <?php
                while ($row = $result->fetch_assoc()) {
                    echo "<tr>";
                        echo "<td class=\"{$row['Day'][0]}day\">{$row['Date']}</td>";
                        echo "<td class=\"{$row['Day'][0]}day\">{$row['Day']}</td>";
                        echo "<td>{$row['Club Meet']}</td>";
                        echo "<td>{$row['Championship']}</td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                        echo "<td></td>";
                    echo "</tr>";
                }
                ?>
            </tbody>
        </table>
        <?php
    }
}

The SQL query is the workhorse of this script. By generating a "derived table" containing all of the dates in the current month (in order), then LEFT JOINing your actual database table data to it and GROUPing on event_date_start, like-data can be grouped together, fully prepared, and delivered in a tight result set.

Take notice to my class/css trickery with {$row['Day'][0]}day <- the [0] means "only display the first character. This makes class attributes like Mday, Fday, etc. To avoid the conditional check, I'm assigning Saturdays and Sundays with Sday so that the styling is simple.

If you want to know what the rendered query looks like for this month:

Derived Table PHP Demo

I didn't bother adding the unused columns while I was developing this solution, but here is the data I used to test with:

CREATE TABLE IF NOT EXISTS `eventcalendar` (
  `event_description` varchar(255) NOT NULL,
  `event_date_start` date NOT NULL,
  `event_date_end` date NOT NULL,
  `event_type` varchar(255) NOT NULL,
  `swim_club_assigned` varchar(255) NOT NULL,
  PRIMARY KEY (`event_description`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `eventcalendar` (`event_description`, `event_date_start`, `event_date_end`, `event_type`, `swim_club_assigned`) VALUES
('Another Championship one!', '2018-10-11', '2018-10-11', 'GCRSA Championship', 'My Club 2'),
('Another one!', '2018-10-11', '2018-10-11', 'GCRSA Club Meet', 'My Club'),
('Dunno Invitational - Template: Z', '2018-11-12', '2018-11-12', 'GCRSA Championship', ''),
('Preparation Meet (LC) - Template: A', '2018-10-01', '2018-10-01', 'GCRSA Club Meet', 'Gold Coast North SC Inc'),
('Reg Winter Championships (SC) - Template: XY', '2018-10-31', '2018-10-31', 'GCRSA Championship', ''),
('Sprint Meet (LC) - Template: H', '2018-10-11', '2018-10-11', 'GCRSA Club Meet', 'Bond SC Inc');

And this is what it made: