Davide Fish Davide Fish - 7 months ago 75
SQL Question

mysql php private message system duplicatee rows

Hello everyone I have a problem in mysql I have to do a search on two user1 and user2 fields with the same variable (3) and I would not not copy the two fields

table name pm:

id user1 user2 message timestamp user1read user2read
1 3 4 edfdfdfdf 1 yes no
2 3 5 ererererer 1 yes no
3 5 3 gfffgfgdfdfd 1 yes no
4 6 3 ewwwewewe 1 yes no
5 7 3 ewwwewddfdfewe 1 yes no
6 3 7 ewwwewddffewe 1 yes no


I tried so

$q= mysqli_query("SELECT DISTINCT user1,user2 FROM pm WHERE (user1='3') or (user2='3') ") or die(mysql_error());


and he print:

user1 user2
3 4
3 5
5 3
6 3
7 3
3 7


I want print

user1 user2
3 4
3 5
6 3
7 3

Answer

please give this query a try.

SELECT user1,user2
FROM pm T1 
INNER JOIN 
  (SELECT MIN(id) as min_id,
         LEAST(user1,user2) as id1,
         GREATEST(user1,user2) as id2
  FROM pm
  WHERE user1 = 3
  OR user2 = 3
  GROUP BY id1,id2)T2
ON T1.id = T2.min_id
ORDER BY T1.id ASC

http://sqlfiddle.com/#!9/8fd88/1

What the query does is with a subquery it finds minimum id of userid 3's conversations with other people grouping by smaller userid and larger userid. Then it joins back with pm table grabbing the user1 and user2 ids of these minimum ids.

What you're looking for is essentially userids of first messages between userid 3 and other people.