Getting Mutual Friends??

[eluser]flyenig[/eluser]
Hi IM trying to find out how i can get mutual friends

im currently having problems thinking this out.

I have a table called "users" and this is how it looks

Code:

id | name
-----------
1   Kenny
2   Jack
3   Jimmy
4   Chris
5   Meg
6   Jake
7   Micheal
8   Dude

I have a table called "friendship"
and this is how it looks

Code:

user_a | user_b
----------------
4         1        
7         5
8         1
2         4
2         1
5         2
1         6
1         7

user_a sends user_b a request to become friends and... BAM there are friends.
Now if im user 2, and i go to user 1s friend list, i want to see what friends we have in common. What is the correct sql to do that?

[eluser]Satlavida[/eluser]
Here u will have to go with 2 WHERE statments.
$id is the logged in user id
and $pid is the id of the user who page you are viewing.

Code:

//Lets take the $id as
$id = "2";
//Lets take the $pid now
$pid = "4";

Code:

"SELECT * FROM friendship WHERE user_a = $id AND WHERE user_b = $pid"

This should result
Kenny
even im trying the same thing i guess thsshould work ...

PLease correct me if im wrong.

[eluser]duellsy[/eluser]
You'll need to cater for the reverse as well, so extend satlivida's query like this

Code:

"SELECT * FROM friendship WHERE (user_a = $id AND WHERE user_b = $pid) OR (user_b = $id AND WHERE user_a = $pid)"

[eluser]Satlavida[/eluser]
[quote author="duellsy" date="1297003671"]You'll need to cater for the reverse as well, so extend satlivida's query like this

Code:

"SELECT * FROM friendship WHERE (user_a = $id AND WHERE user_b = $pid) OR (user_b = $id AND WHERE user_a = $pid)"

[/quote]
Hey thanks for helping me further to development

[eluser]duellsy[/eluser]
No probs

[eluser]kre8ivdesigns[/eluser]
This is how I setup my friends list. I'm still a newbie at PHP;

Mysql Database

Friends table

id
user_id
friend_id
accepted

users table
name
surname
....

user_profiles table
thumbnail

Code:

function view_friends($id)
{
   $this->db->select('
                    friends.id,
                    friends.user_id,
                    friends.friend_id,
                    friends.accepted,
                    users.name as firstname,
                    users.surname as lastname,
                    u.name as fname,
                    u.surname as lname,
                    user_profiles.thumbnail as image,
                    up.thumbnail as img
                 ');
     $this->db->where('friends.user_id',$id);
     $this->db->or_where('friends.friend_id',$id);
     $this->db->join('users','users.user_id=friends.user_id','left');
     $this->db->join('user_profiles','user_profiles.user_id=friends.user_id','left');
     $this->db->join('users AS u','u.user_id=friends.friend_id','left');
     $this->db->join('user_profiles AS up','up.user_id=friends.friend_id','left');
    
     $query = $this->db->get('friends');

     if($query->num_rows() > 0)
     {
       return $query->result();
     }
}

To show friends list I have the following:

Code:

<?php if(!empty($friends)) { ?>
  <?php foreach($friends as $friend) : ?>
       <?php if(($friend->accepted == 1) && ($friend->friend_id != $this->user->user_id)) { ?>
        <li>Then it shows the friend</li>
       &lt;?php } elseif(($friend->accepted == 1) && ($friend->user_id != $this->user->user_id)) { ?&gt;
        <li>Then it shows the friend</li>
&lt;?php } ?&gt;
   &lt;?php endforeach: ?&gt;
&lt;?php } else { echo 'No Friends' } ?&gt;

Always looking for feedback....