Question 40352 in PHP Question posted in
Official manual can be found here.

How to group rows by month, loop days inside the month and skip to next month using PHP & SQL

Hi I’m trying to achieve something with my loop from my database and I don’t have the solution. I think I’m close to the solution but really need help. I believe I need to make a loop in a loop but not sure how to do that.

How can I group rows by month, loop days inside the months and skip to next month using PHP & SQL?

The data looks like this:

Creation_date | Project_name
2023-02-24 | Project A
2023-02-22 | Project B
2023-02-22 | Project C
2023-02-21 | Project..
2023-02-13 | Project..

2023-01-28 | Project..
2023-01-20 | Project..

2022-12-31 | Project..
2022-12-02 | Project..

2022-11-09 | Project..

2022-10-26 | Project..

Here is the code

<?php
  $query = '
    SELECT
    Creation_date,
    DATE_FORMAT(Creation_date, "%m") Creation_date_m,
    DATE_FORMAT(Creation_date, "%M") Creation_date_M_full,
    DATE_FORMAT(Creation_date, "%Y") Creation_date_Y_full,
    Project_name
    FROM Projects
    ORDER BY Creation_date DESC
  ';
  $result = $conn->query($query);

  $previousMonth = null;
  $currentMonth = date('m');
?>

<?php while ($row = $result->fetch_assoc()): ?>
  <?php
    $month = $row['Creation_date_m'];
    $monthFull = $row['Creation_date_M_full'];
    $year = $row['Creation_date_Y_full'];

    if (!$previousMonth) {
      $previousMonth = $currentMonth;
    }

    // if there is a change of month, define the class to be set
    if ($month != $previousMonth) {
      $previousMonth = $month;
      $monthName = '<h2>' . $monthFull . ' ' . $year . '</h2>';
    } else {
      $monthName = "";
    }
  ?>

  <div class="month">
    <?= $monthName; ?>
    <div class="day-of-month"><?= $row['Creation_date']; ?></div>
  </div>
<?php endwhile; ?>

Right now my loop output something like this:

<div class="month">
  <h2>February 2023</h2>
  <div class="day-of-month">2023-02-24</div>
</div>

<div class="month">
  <div class="day-of-month">2023-02-22</div>
</div>

<div class="month">
  <div class="day-of-month">2023-02-22</div>
</div>

<div class="month">
  <h2>January 2023</h2>
  <div class="day-of-month">2023-01-28</div>
</div>

<div class="month">
  <div class="day-of-month">2023-01-20</div>
</div>

But It should output something like this:

<div class="month">
  <h2>February 2023</h2>
  <div class="day-of-month">2023-02-24</div>
  <div class="day-of-month">2023-02-22</div>
  <div class="day-of-month">2023-02-22</div>
</div>

<div class="month">
  <h2>January 2023</h2>
  <div class="day-of-month">2023-01-28</div>
  <div class="day-of-month">2023-01-20</div>
</div>

Thank you so much, I hope it’s clear 🙂

Tags:

2

Answers


  1. 0

    You need to only output the month div if it changes. Something like this (untested) code:

    <?php while ($row = $result->fetch_assoc()): ?>
  <?php
    $month = $row['Creation_date_m'];
    $monthFull = $row['Creation_date_M_full'];
    $year = $row['Creation_date_Y_full'];

    // if there is a change of month, define the class to be set
    if ($month != $previousMonth) {
      $monthName = '<div class="month"><h2>' . $monthFull . ' ' . $year . '</h2>';
      $monthEnd = '</div>';
    } else {
      $monthName = "";
      $monthEnd = "";
    }
    echo $monthName; 
    ?>
    <div class="day-of-month"><?= $row['Creation_date']; ?></div>
    <?php
    $previousMonth = $month; // set it here so it changes every time.
    echo $monthEnd;
endwhile; ?>

    If the month changes, $monthName contains the header and $monthEnd contains the closer, otherwise they’re both empty.

    You don’t need to check if $previousMonth is null, because it’ll still be different to the month you get back from the database. You do need to change it every time you output a row, not just every time you output a header.

    I’m not sure where $rowCountTable came from, so I changed it to $row inside the loop. Maybe a typo on here.

    Login or Signup to reply.
  2. 0

    I’m no php programmer (at all), but the basic structure should be…

    • read next row from db
    • if row is for a different month, write a <div> AND a <div>
    • write the div for the current db row
    • loop back to read all db rows
    • write a final closing div for the last month

    The only caution to the above is to make sure you done write a closing div before the first opening div.

    The below should do that, if I understand the php syntax correctly.

    • untested, written on my phone
    • others should feel free to edit stupid mistakes
    <?php
  $query = '
    SELECT
    Creation_date,
    DATE_FORMAT(Creation_date, "%m") Creation_date_m,
    DATE_FORMAT(Creation_date, "%M") Creation_date_M_full,
    DATE_FORMAT(Creation_date, "%Y") Creation_date_Y_full,
    Project_name
    FROM Projects
    ORDER BY Creation_date DESC
  ';
  $result = $conn->query($query);

  $previousMonth = "";
  $closeMonth = "";
?>
<?php
  while ($row = $result->fetch_assoc()):
?>
  <?php
    $month = $row['Creation_date_m'];
    $monthFull = $row['Creation_date_M_full'];
    $year = $row['Creation_date_Y_full'];

    // if the month has changed
    // - record the month in previousMonth
    // - close the preceding month, if there was one
    // - open the new month
    if ($month != $previousMonth) {
      $previousMonth = $month;
      echo $closeMonth;
      echo '<div class="month">' ;
      echo '<h2>' . $monthFull . ' ' . $year . '</h2>';
      $closeMonth = '<div>';
    }
  ?>
    <div class="day-of-month"><?= $row['Creation_date']; ?></div>
<?php
  endwhile;
  echo $closeMonth
?>
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search