title: How to group database table data by day and create a calendar with an html table?
body: <p>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.</p>
  
  <p>Here's a few sample rows:</p>
  
  <p><a href="https://i.stack.imgur.com/0byLi.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/0byLi.png" alt="enter image description here"></a></p>
  
  <p>Here is my code to retrieve array</p>
  
  <pre><code>$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 "&lt;br&gt;";
  
  // 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-&gt;fetch_object()) {
          $r[] = $row;
      }
      $result-&gt;close(); // Free result set
  }
  </code></pre>
  
  <p>print_r($r);   gives me the array below</p>
  
  <pre><code>Array (
      [0] =&gt; stdClass Object (
          [event_date_start] =&gt; 2018-10-01
          [event_date_end] =&gt; 2018-10-01
          [event_description] =&gt; Preparation Meet (LC) - Template: A
          [event_type] =&gt; GCRSA Club Meet
          [swim_club_assigned] =&gt; Gold Coast North SC Inc
      )
      [1] =&gt; stdClass Object (
          [event_date_start] =&gt; 2018-10-11
          [event_date_end] =&gt; 2018-10-11
          [event_description] =&gt; Sprint Meet (LC) - Template: H
          [event_type] =&gt; GCRSA Club Meet
          [swim_club_assigned] =&gt; Bond SC Inc
      )
      [2] =&gt; stdClass Object (
          [event_date_start] =&gt; 2018-10-31
          [event_date_end] =&gt; 2018-10-31
          [event_description] =&gt; Reg Winter Championships (SC) - Template: XY
          [event_type] =&gt; GCRSA Championship
          [swim_club_assigned] =&gt; 
      )
  ) 
  </code></pre>
  
  <p>I want to be able to retrieve from the array (where the event type = <code>$something</code> and the start date = <code>$a_date</code>  and then display the <code>$swim_club</code> and the <code>$event_description</code>.</p>
  
  <pre><code>while($row = fetch_assoc($r)) {
      if (($row["event_type"] == 'GCRSA Championship') &amp;&amp; ($row["event_date_start"] == $day)){
          echo  "{$row['swim_club_assigned']} {$row['event_description']}";
      }
  }
  </code></pre>
  
  <p>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.</p>
  
  <p>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)</p>
  
  <pre><code>&lt;table class="table table-bordered"&gt;
      &lt;thead&gt;
           &lt;tr&gt;
              &lt;th rowspan="2" colspan="2"&gt;Date&lt;/th&gt;
              &lt;th colspan="3"&gt;GCRSA Region&lt;/th&gt;
              &lt;th colspan="2"&gt;State, SAL, Intl&lt;/th&gt;
              &lt;th colspan=2&gt;&lt;center&gt;Other events&lt;/center&gt; &lt;/th&gt;
           &lt;/tr&gt;
           &lt;tr style="border-bottom: 1pt Darkblue;"&gt;
              &lt;th&gt;Club meet&lt;/th&gt;
              &lt;th&gt;Championship&lt;/th&gt;
              &lt;th&gt;Development&lt;/th&gt;
              &lt;th&gt;SQ&lt;/th&gt;
              &lt;th&gt;Sal&lt;/th&gt;
              &lt;th&gt;School Meets&lt;/th&gt;
              &lt;th&gt;Qld School Holidays&lt;/th&gt;
            &lt;/tr&gt;
      &lt;/thead&gt;
      &lt;tbody&gt;
          &lt;?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 &lt; date('t', strtotime($query_date)) + 1; $i++){
          ?&gt;
              &lt;tr&gt;
              &lt;td &lt;?php if (date('w',strtotime($day)) == 0 || date('w',strtotime($day)) == 6 ){
                  echo "style='background-color:red;'";
              }?&gt;&gt;&lt;?php echo date('M d, Y',strtotime($day)); 
  
              ?&gt;&lt;/td&gt;
              &lt;td &lt;?php if (date('w',strtotime($day)) == 0 || date('w',strtotime($day)) == 6 ){
                  echo "style='background-color:red;'";
              }?&gt;&gt;&lt;?php echo date('l',strtotime($day)); ?&gt;&lt;/td&gt;
  
              &lt;td&gt; &lt;?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') &amp;&amp; ($row["event_date_start"] == $day)){
                      echo  "{$row['swim_club_assigned']} {$row['event_description']}";
                  }
              }
              ?&gt;&lt;/td&gt;
  
              &lt;td&gt; &lt;?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') &amp;&amp; ($row["event_date_start"] == $day)){
                      echo  "{$row['swim_club_assigned']} {$row['event_description']}";
                  }
              }
              ?&gt;&lt;/td&gt;
  
          &lt;?php
              for($j =1;$j&lt;=5;$j++){
                  echo "&lt;td&gt;&lt;/td&gt;".PHP_EOL;
              }
          ?&gt;
          &lt;/tr&gt;
          &lt;?php 
          $day = date('Y-m-d', strtotime('+1 day', strtotime($day)));
          } 
          ?&gt;                                    
      &lt;/tbody&gt;
  </code></pre>