在发送时对邮件进行分组

Question

I'm developing a message system between two users and I want to group them by the time of sending, showing it only once.

Currently, I show messages and the time each of the messages was sent, but I want to show only the last message grouping them in the conversation (as it happens with the direct messages of Twitter).

SELECT id, author, datetime, text FROM messages WHERE author = 'A' AND receiver = 'B'

What I have (id / author / datetime / text):

1 / A / 22/03/2019 22:15 / This is message 1
--------------------------------------------
2 / A / 22/03/2019 22:16 / This is message 2
--------------------------------------------
3 / B / 22/03/2019 22:16 / This is message 3
--------------------------------------------
4 / A / 22/03/2019 22:16 / This is message 4
--------------------------------------------
5 / A / 22/03/2019 22:16 / This is message 5
--------------------------------------------
6 / A / 22/03/2019 22:18 / This is message 6
--------------------------------------------
7 / B / 22/03/2019 22:19 / This is message 7

What I want (id / author / datetime / text):

1 / A / 22/03/2019 22:15 / This is message 1
--------------------------------------------
2 / A / 22/03/2019 22:16 / This is message 2
--------------------------------------------
3 / B / 22/03/2019 22:16 / This is message 3
--------------------------------------------
4 / A / 22/03/2019 22:16 / This is message 4
5 / A / 22/03/2019 22:16 / This is message 5
--------------------------------------------
6 / A / 22/03/2019 22:18 / This is message 6
--------------------------------------------
7 / B / 22/03/2019 22:19 / This is message 7

Messages 4 and 5 have been grouped because they belonging to the same user and were sent in the same minute.

I would like to group messages by days when the current date is not equal to the date of sending the messages. In other words, the messages that were not sent on the current date would be grouped together. In this case the user of the message would not be taken into account and all would be grouped only by date.

Answer 1

You don't have to change anything with your query to get that result, you can just change how you fetch the rows.

Use the author and datetime values to create a composite key to separate your messages into groups. You can also get the date part of the datetime to separate the message groups into days.

while ($row = $query->fetch_assoc()) {

    // get the date
    $date = substr($row['datetime'], 0, 10);

    // create the composite key
    $key = "$row[author]$row[datetime]";

    // group the messages
    $message_groups[$date][$key][] = $row;
}

I would also recommend specifying an ORDER BY for your query, probably ORDER BY id. MySQL won't necessarily automatically return them in the order they were inserted.