PHP MySQL loop through the rows and print date when the date changes

My messages table is like this:

id : (auto increment)
incoming_msg_id : (receiver's user id, BIGINT)
outgoing_msg_id : (sender's user id, BIGINT)
msg : (message content, LONGTEXT)
created_at : (timestamp)

This is my query used for fetching messages:

$stmt = $pdo->prepare("SELECT * FROM messages
                       LEFT JOIN users ON users.id = messages.outgoing_msg_id
                       WHERE (outgoing_msg_id = :omsg AND incoming_msg_id = :imsg) OR (outgoing_msg_id = :imsg AND incoming_msg_id = :omsg)
                       ORDER BY messages.id ASC");
$stmt-> bindValue(':imsg', $imsg);
$stmt-> bindValue(':omsg', sessionUser());
$stmt-> execute();

I want to use created_at timestamp field to group messages in such a way so that I can display date when the message transactions took place.

For example:

5th July, 2021

Message 1
Message 2

6th July, 2021

Message 3
Message 4

I therefore want to print a new header containing date each time the date changes. My current PHP loop goes like this:

while($f = $stmt->fetch()){

  // HERE, I WANT TO CHECK IF DATE CHANGES AND PRINT A NEW HEADER, FOR EXAMPLE
  // if(dateChanges){ echo date('jS M, Y', strtotime($f['created_at'])); } // UNSURE, OF LOGIC INSIDE IF STATEMENT

  if($f['outgoing_msg_id'] == sessionUser()){
    $html .= "<div class='chat outgoing'>
                <div class='details d-flex'>
                  <div class='textmsgbox'>
                    <div class='thechatmsg'>".decrypt($f['msg'], ENCRYPTION_KEY)."</div>
                    <div class='tmbcontent d-flex'>
                      <small>".date('h:i a', strtotime($f['created_at']))."</small>
                      <small class='seen'>$seen</small>
                    </div>
                  </div>
                </div>
              </div>";
  }else{
    $html .= "<div class='chat incoming'>
                <div class='details d-flex'>
                  <div class='textmsgbox'>
                    <div class='thechatmsg'>".decrypt($f['msg'], ENCRYPTION_KEY)."</div>
                    <div class='tmbcontent'>
                      <small>".date('h:i a', strtotime($f['created_at']))."</small>
                    </div>
                  </div>
                </div>
              </div>";
  }
}

Answer

Very simple. First you need to declare a null variable outside of the loop and then check the timestamp’s date against it and print the date. Then at the end you can update the previously defined null variable with the date from the timestamp and you are done. Have a look at the code below:

$lastDate = null; // DECLARE A NULL VARABLE FOR THE LAST DATE
while($f = $stmt->fetch()){
  $chatDate = null; // DECLARE A NULL VARIABLE HERE TO AVOID MULTIPLE OUTPUTS
  
  // CHECK AGAINST THE LAST DATE IF IT EQUALS TO THE TIMESTAMP'S DATE, IF NOT, PRINT THE DATE
  if($lastDate !== date('Y-m-d', strtotime($f['created_at']))) {

    // I ADDED THIS ADDITIONAL CODE SO THAT YOU CAN PRINT THE DATE AS "TODAY" & "YESTERDAY" 
    // You may remove this if you don't want

    if(date('Y-m-d', strtotime($f['created_at'])) == date('Y-m-d')){
      $cd = "TODAY";
    }else if(date('Y-m-d', strtotime($f['created_at'])) == date('Y-m-d', strtotime('-1 day'))){
      $cd = "YESTERDAY";
    }else{
      $cd = date('jS F, Y', strtotime($f['created_at']));
    }

    $chatDate = "<div class='chat-date'>".$cd."</div>";
  }

  $html .= $chatDate;

  // YOUR IF....ELSE CONDITION GOES HERE
  if(){
    // Your code as in question
  }else{
    // Your code as in question
  }

  // UPDATE THE NULL VARIABLE WITH CURRENT DATE
  $lastDate = date('Y-m-d', strtotime($f['created_at'])); 
}

Done! As simple as that. Enjoy 🙂