CODEWITHSUNDEEP
phpmysql
Coman Paul
Coman Paul

Reputation: 301

MYSQL Display values not joined after joining 2 tables

Table users:

  id firstname lastname    email         avatar
  1     Coman      Paul   [email protected]    ./images/a.png

Table friendships:

 id user1 user2
  1    1     2
  2    1     5
  3    3     1
  4    2     3

User 1 is friend with 2,3,5

User 2 is friend with 1,3

With this query I can display my friends..(user with id=1)

select 
    u.firstname, u.lastname, u.email, u.avatar 
from 
    users u
inner join 
    friendships f on f.user2 = u.id 
where 
    f.user1 = 1

union

select 
    u.firstname, u.lastname, u.email, u.avatar 
from 
    users u
inner join 
    friendships f on f.user1 = u.id 
where 
    f.user2 = 1

What I want to do is to display the rest of users that are not friends with me...Obviously changing = to <> won't work because the friendship between user 2 and 3 can cause displaying them as not friends form be, but in other lines they are my friends...

So how do I do, after I display a list of my friends (with the first query) to display under it, another list of non-friends...

To better understanding click on a post likes on facebook and you will see that first users to shown are your friends..then the rest of them...

There is the php part of code...

$numrows = mysql_num_rows($querym) or die (mysql_error());

if($numrows!=0)
{

Echo '<div id="members">    <ul >';
while ($row = mysql_fetch_assoc($querym))
{


$firstname = $row['firstname'];
$lastname = $row['lastname'];
$email = $row['email'];
$avatar = $row['avatar'];

Echo '<li>......';
       //printing the variables and all data
     }

   }

Upvotes: 2

Views: 52

Answers (1)

Digital Chris
Digital Chris

Reputation: 6202

You want to get all users who are not in friendships with a certain user. But they might show up in one of two ways, so you must make sure they're not in a friendship as user1 AND not in a friendship as user2:

select u.firstname, u.lastname,u.email,u.avatar from users u
where id not in (select user1 from friendships where user2 = 1)
and id not in (select user2 from friendships where user1 = 1)

Edit: you might also want to add:

and id <> 1

to exclude displaying yourself as a non-friend unless this is handled elsewhere.

Upvotes: 2

Related Questions