MYSQL：从表中回显结果，但不回显重复的结果？

Question

I am really struggling with this so hope someone can help. I have a table like so:

id | msg_id | to_user_id | from_user_id | subject | content
1      1          6             10          hi       hello
2      1          6             10          hi       hello
3      1          10            6           hi       hello
4      2          4             1           hi
5      3          1             4           hi

i am wanting to echo out the results of this table with the below query using mysql. At the moment i am getting all the results echoed out fine, but i do not want to echo out duplicate results. In this instance the number '1' appears in the column 'msg_id' four times. I want to use DISTINCT COUNT or something similar to select all the rows from 'msg_id' but to not echo out the duplicates and only count '1' once.

here's my mysql statement:

function get_inbox2() {
                global $connection;
                global $_SESSION;
                $query = "SELECT *
                            FROM ptb_messages m, ptb_profiles p
                            WHERE m.to_user_id =".$_SESSION['user_id']."
                            AND m.to_user_deleted !='".$_SESSION['user_id']."'
                            AND m.from_user_deleted !='".$_SESSION['user_id']."'
                            AND m.from_user_id != '0'
                            AND p.user_id = m.from_user_id
                            ORDER BY m.date_sent DESC";


                            $inbox_set2 = mysql_query($query, $connection);
                confirm_query($inbox_set2);
                return $inbox_set2;
            }

I have tried something like this but it's not showing the results:

$query = "SELECT COUNT(DISTINCT msg_id) totalCOUNT FROM ptb_messages WHERE to_user_id=".$_SESSION['user_id']."";

Answer 1

Try getting only the msg_id with MAX(id) (you can either get the MIN with the same logic) for each id:

SELECT m.*,p.*
FROM ptb_messages m
INNER JOIN ptb_profiles p ON p.user_id = m.from_user_id
INNER JOIN (
  SELECT MAX(id) AS id,msg_id 
  FROM messages 
  GROUP BY msg_id
) a ON a.id = m.id
WHERE m.to_user_id = ".$_SESSION['user_id']."
  AND m.to_user_deleted != '".$_SESSION[' user_id ']."'
  AND m.from_user_deleted != '".$_SESSION[' user_id ']."'
  AND m.from_user_id != '0'
ORDER BY m.date_sent DESC