How to group private messages?

Question

PHP

Good day to all! I have a private messaging system on my site. Tell me, is it possible to somehow display a block for each unique interlocutor? Like for example in VKontakte/Facebook. That is, there is a block with the last message from a certain user, when you click on the block, I get to the page with all the messages from him.

<?php
    if(empty($_GET['to'])){
        echo "Please select message-user!";
        $my_id = $user_data['user_id'];
        $query = mysql_query("SELECT * FROM `messages` WHERE `from` = $my_id ORDER BY `id` DESC") or die(mysql_error());
        $row = mysql_fetch_array($query);
        echo '<br>Last message <br>';
        echo $row['message'];
    }
    if(isset($_POST['send_message'])){
        $message = $_POST['message'];
        $to = $_POST['to'];
        $from = $user_data['user_id'];
        $date = date('Y.m.d');
        $time = date("H:i:s");
        $query = mysql_query("INSERT INTO `messages` (`to`,`from`,`message`,`date`,`time`) VALUES ('$to','$from','$message','$date','$time')") or die(mysql_error());
    }
                    if(!empty($_GET['to'])){
                        $to_user = $_GET['to'];
                        $query = mysql_query("SELECT * FROM users where user_id = $to_user") or die(mysql_error());
                        $row = mysql_fetch_assoc($query);
                        if(!empty($row['username'])){
                            $username = $row['username'];
                            echo "Dialog with <a href='user/$username'>$username</a>:";
                            $from = $user_data['user_id'];
                            $to = $_GET['to'];
                            $query = mysql_query("SELECT * FROM `messages` WHERE `from` = $from AND `to` = $to OR `from` = $to AND `to` = $from ORDER BY `id` DESC") or die(mysql_error());
                            $messages = mysql_fetch_array($query);?>
                            <div class="messages-box">
<?php
                                if(!empty($messages)){
                                    do{
                                        $from_username_id = $messages['from'];
                                        $query_get_name = mysql_query("SELECT * FROM `users` WHERE `user_id` = '$from_username_id'") or die(mysql_error());
                                        $row = mysql_fetch_assoc($query_get_name);
                                        $from_username = $row['username'];
                                        echo "<div class='message'>";
                                        echo "<div class='right'>";
                                        echo $messages['date'];
                                        echo '</div>';
                                        echo "<a href='user/$from_username'>";
                                        echo $from_username;
                                        echo '</a>:<br>';
                                        echo $messages['message'];?>
                                        <div class='right'>
                                        <?php
                                        echo $messages['time'];
                                        echo "</div>";
                                        echo "</div>";
                                    }while($messages = mysql_fetch_array($query));
                                }else{
                                        echo "Dialog is empty!";
                                    }?>
                            </div>
                                <form action="" method="POST">
                                    <div align='center'>
                                        <textarea name="message" id="" cols="45" rows="7"></textarea>
                                    </div>
                                    <input type="hidden" name="to" value="<? echo $_GET['to'];?>">
                                    <input type="Submit" name="send_message" value="send">
                                </form>
<?php
                        }

I hope the point is clear. Please tell me how to implement my idea? Thanks in advance!

Answer 1

group messages with GROUP BY to get a list of conversations

Answer 2

I can give a piece of code, from a selection of messages by dialogue:

$criteria = new CDbCriteria;
$criteria->select = "t.* ,(SELECT COUNT(*) FROM {{messages}} WHERE IF(t.userTo=:userid,userTo,userFrom)=t.userTo AND IF(t.userTo=:userid,userFrom,userTo)=t.userFrom AND isNew=1) AS newMessagesdialog";
$criteria->join = "JOIN (SELECT IF(userTo=:userid,userFrom,userTo) AS user_id_other, MAX(creationDate) AS date_time_max FROM pdb_messages 
WHERE (userTo=:userid OR userFrom=:userid) AND IF(userTo=:userid,hideOnUserTo,hideOnUserFrom)=0  AND moderated_status=1 AND IF(type='s' AND userFrom=:userid,false,true) GROUP BY IF(userTo=:userid,userFrom,userTo)) AS d ON IF(userTo=:userid,userFrom,userTo)=user_id_other AND creationDate=date_time_max ";
$criteria->addCondition('userTo=:userid OR userFrom=:userid');
$criteria->order = 'newMessagesdialog DESC,t.creationDate DESC';
$criteria->params = array(':userid' => $userid);

The code is sharpened for Yii, but in general, if you figure it out, the essence of the selection is visible