how to select data from many to many relationship?

[eluser]arro[/eluser]
good afternoon, I'm very confused with my database for three days..so I want to ask here.
I have two table here..my table about friendship

users
-------------------
id username
1 Antony
2 Brown
3 Carr
4 Dunn
5 Edward
6 Foster
7 Gerrard

friends
-------------------
id id_a id_b status
1 1 2 friend
2 1 5 friend
3 3 4 friend
4 2 5 friend
5 1 3 friend
6 1 7 friend
7 5 7 friend
8 4 1 friend
9 1 6 wait

1.take look at table friends,if I was Brown(id=2),I have 2 friends(Antony=1,Edward=5)
2.if I go to Carr(id=3) page, how can I only select one row (id_a =2 and id_b=3) or (id_a =3 and id_b=2)from table so I know that Carr is not my friend so i can add him??


my model :

Code:

function addfriend($id){
  $this->db->select('*');
  $this->db->from('users');
  $this->db->join('friends','users.id = friends.id_b or users.id = friends.id_a','left'); //here is many to many CMIIW
  $this->db->where('friends.id_a',$id);
  $this->db->or_where('friends.id_b',$id);
  $this->db->group_by('users.id');
  $query = $this->db->get();
  return $query->result();
}

nb:I use foreach and all result was showed...
sorry for my bad english...
Thank for attention..

[eluser]Krzemo[/eluser]
Well,
If just want to check if someone is your friend just query friends table:

Code:

SELECT status FROM friends WHERE id_a = $your_id  AND id_b = $someones_id;

join makes sense only when you want to get list of users with friends status...

[eluser]Cristian Gilè[/eluser]
Hi arro,

you have to check if the row (id_a =2 and id_b=3) or the row (id_a =3 and id_b=2) exist.

Code:

function addfriend($my_id,$id)
{
    $this->db->where('id_a',$my_id);
    $this->db->where('id_b',$id);
    $query_1 = $this->db->get('friends')->num_rows();

    $this->db->where('id_a',$id);
    $this->db->where('id_b',$my_id);
    $query_2 = $this->db->get('friends')->num_rows();

    if($query_1 + $query_2 == 0)
    {
      //insert $id as your friend
    }  
}

I have splitted the query in two parts to make it plain because I didn't want to mess with OR and AND condition.
For complicated query AR is not always the best solution.

Cristian Gilè

[eluser]arro[/eluser]
@cshamoh : Now I used single row in my table only $my_id and $friend_id...I tried wit my logic and that work..thanks for the reply

@cristian Gilè : yeah that simple way to join the table but now I only use one row..for that the column status is very important... thanks...

[SOLVED]