Sql select all conversation by group

Question

In massage system I want to select all conversation by a user.

My msg table is like:

id  | mfrom | mto  | date
=========================
1   | 10    | 12   | 0000
2   | 14    | 10   | 0000
3   | 10    | 14   | 0000
4   | 16    | 10   | 0000
5   | 10    | 16   | 0000

Here my user ID say: 10. So as above I have 3 conversation with user ID 12, 14, 16

I used this sql to got user id have conversation with me:

$results = mysqli_query($db,"SELECT mfrom FROM msg WHERE `mto`='10' GROUP BY `mfrom` ORDER BY `date` DESC");
while($rows = mysqli_fetch_assoc($results)) {
    $mfroms[] = $rows['mfrom'];
}

$results = mysqli_query($db,"SELECT mto FROM msg WHERE `mfrom`='10' GROUP BY `mto` ORDER BY `date` DESC");
while($rows = mysqli_fetch_assoc($results)) {
    $mtos[] = $rows['mto'];
}

$result = array_unique(array_merge($mfroms,$mtos), SORT_REGULAR);

Here above query gave me a array result 12,14,16

Now my problem is, I can`t fetch all conversation with me by group. That means, I want to get result like as below from above table:

user 12 conversation with user 10(me) total 1 display recent massage only
-------------------------------------------------------------------------
user 14 conversation with user 10(me) total 2 display recent massage only
-------------------------------------------------------------------------
user 16 conversation with user 10(me) total 2 display recent massage only

Answer 1

Replace AccountTable by your table name, 10 as your user id and test this.

SELECT s.user1,s.user2,count(*) as recent_messages
FROM (
    SELECT CASE WHEN t.mfrom > t.mto THEN t.mfrom ELSE t.mto END as user1,
           CASE WHEN t.mfrom < t.mto THEN t.mfrom ELSE t.mto END as user2
    FROM AccountTable t WHERE t.mfrom = 10 OR t.mto=10) s
GROUP BY s.user1,s.user2