How to remove duplicate blocks with the interlocutor?

Question

PHP

Good day to all! Please tell me how to display a block for each dialogue, so that the opposite combinations of interlocutors are not repeated.
The code itself:

$dialogs = mysql_query("SELECT *,COUNT(id) as count FROM `messages`
WHERE `from` = '$my_id' OR `to` = '$my_id'
GROUP BY `to`,`from` ORDER BY `date` DESC") or die(mysql_error());
  while ($row = mysql_fetch_assoc($dialogs)) {
    $my_id = $user_data['user_id'];
    if($my_id === $row['to']) {
      echo "mnie";
      $to_me = '1';
        $to_id = $row['from'];
      ?>

      <?php
    }if($my_id != $row['to']) {
      echo "nie mnie";
      $not_to_me++;
      $to_id = $row['to'];
    }
      ?>
      <a href='?to=<?php echo $to_id;?>' style='text-decoration:none;'>
      link
      </a>
      <?php
    
    echo $row['from'];
    echo "->";
    echo $row['to'];
    echo "<br>";
  }

and how it looks:

while the links are formed correctly, that is, ?to=$idI get to the page, to the correct dialog. I am number 7.
Thanks in advance!

Answer 1

Perhaps this is a chat test case. First implementation.
What did you want to show in the message list? All the messages that someone wrote to someone? Then it works correctly. The counter is just not used and for some reason the table has a dialog_id, but you don’t use it either, but instead group by from + to.
For convenience, I would visually build the chat in a different way.
Look at this mockup https://icons8.com/2015/07/17/icons8-wpf-ui-framework/ , or rather, this screen take.ms/5F5rr
On the left side is a list of contacts to whom you recently wrote or who replied you.
To build it, you need such a request

SELECT DISTINCT A.`user_id`
FROM(
   SELECT m1.`from` as `user_id`, m1.`date` FROM `messages` m1 WHERE m1.`to` = 7
   UNION
   SELECT m2.`to` as `user_id`, m2.`date` FROM `messages` m2 WHERE m2.`from` = 7
   ORDER BY `date` DESC
) A

That is, a list of interlocutors in descending order of the date of the last message.
If you need a message counter, then count for each interlocutor or dialogue.
If you need a counter of unread messages, then you need to store the identifier of the last read message for each interlocutor or dialogue and count the number of messages with an identifier greater than this.
Maybe this is a lot of requests to the database, but I made them different for each necessary element.
Under the list of interlocutors + message counter - one request
If you need a counter of unread messages, then the second request or a series of requests, although you can try to fit into the first one.
Under the chat messages of a specific dialogue - the third request.
As a test example - norms. No questions.
If in industrial exploitation, then it is unprofitable to store individual messages in a relational database. Too many table entries. It is advisable to use a NoSQL database and store one entire dialog in one record of the database.