database modeling for a friend list

[eluser]pabloheim[/eluser]
Im working a project and I have already implemented an auth library and all works fine. what I want to do now is to generate the possibility for each user to add friends to a list.

My question is how can I model it in a database, for example:

if there is a table named friends with 3 fields :
id1 : id of a user
id2 : id of another user
status : 0 or 1. if -> 1 id1 and id 2 are friends. if 0 id2 hasnt confirmed yet.

The idea is the following: if I invite a user to be my friend, a new row in this table is created , with the field status =0 and id1 = "my id" and id2= "the id of the other user". if he accept , then status = 1.

But this model has a problem, because its pretty difficult to retreive my friends list , since I have to search in both id1 an id2 fields.

do you have some ideas or a better model ?

thanks a lot!
sorry for my english

[eluser]Jay Logan[/eluser]
What's so difficult?

Code:

function retrieve_friends($my_id)
{
$this->db->select();
$this->db->from('friends_table');
$this->db->where('friends_table.status', '1');
$this->db->where('friends_table.id1', $my_id);
$this->db->or_where('friends_table.id2', $my_id);
$this->db->join('users_table', 'users_table.id = friends_table.id1', 'left');
$this->db->join('users_table', 'users_table.id = friends_table.id2', 'left');
$get_friends = $this->db->get();
$friends = $get_friends->result_array();
$i = 0;
foreach ($friends as $friend) {

if ($friend['id'] != $my_id) {
$key = $i++;
$friends_list[$key] = $friend['first_name'];
$friends_list[$key] = $friend['last_name'];
$friends_list[$key] = $friend['email_address'];
}
return $friends_list;
}

Or something like that. I'm still new to this. But you basically should build an array with whatever friend info you want. That's what I would do. This is untested BTW. Just felt like typing out code from my phone while I wait on this plane. Good luck.

CodeIgniter is fun.

[eluser]Krzemo[/eluser]
I guess your model is the only proper one.
Why do you have to search both id fields if you are looking only for friends?

For friends of only one user below should work:

Code:

SELECT u.name
FROM users u, friends f
WHERE u.id = f.friend_id
AND f.user_id = <your_user_id>
AND f.status = 1

For friends all users try smth like that:

Code:

SELECT u1.name AS user_name
, u2.name AS friend_name
FROM users u1, friends f, users u2
WHERE u1.id = f.user_id
AND u2.id = f.friend_id
AND f.status = 1

Im old fashioned SQL quy, but translating it to AC shouldnt be a problem.

Regs

[eluser]bretticus[/eluser]
[quote author="J-Slim" date="1239940993"]Just felt like typing out code from my phone while I wait on this plane[/quote]

Holy crap! You typed that out on your phone? I could never do that in a million years. If I tried, and succeeded, it'd take me a million years too. :cheese:

[eluser]slowgary[/eluser]
What's the big deal? I write code for planes all the time. A few Boeing planes are using code that I wrote. And so far, only a few crashes

[eluser]Thorpe Obazee[/eluser]
[quote author="slowgary" date="1239954888"]What's the big deal? I write code for planes all the time. A few Boeing planes are using code that I wrote. And so far, only a few crashes[/quote]

I'm never riding a Boeing from now on

[eluser]bretticus[/eluser]
[quote author="slowgary" date="1239954888"]What's the big deal? I write code for planes all the time. A few Boeing planes are using code that I wrote. And so far, only a few crashes[/quote]

Those must have been the result of subroutines you wrote using your iphone! :-)

[eluser]pabloheim[/eluser]
wow. thanks to all ! i used a mix of solutions posted here and now it works!

[eluser]bluepicaso[/eluser]
Well hey all, I'm new to this friend list stuff. I recently code a project with friends list.
I thought of building friends table as same like of pabloheim above. BUt i was also thinking of the redundancy. it will create. In my case its just follow a user, the other user needs not to approve you.
mY CURRENT FRIEND TABLE STRUCTURE

--------------------------------
frnd_id | user_id | frnds |
---------------------------------

The first column saved the auto generated id.
the next column "user_id" saves the value of the user
and the last column saves the string with IDs of other users containing "|" as a separator.
I was able to search users though and was also able to add them to a user.
But the problem starts when user searched and I'm unable in not showing user the same friends that he has in his list.

Please suggest me a better option.
IS MY DATABASE STRUCTURE CORRECT?
If not than can i switch over to pabloheim's structure above.

forgive me for my foolishness but please reply soon
Thank you.

[eluser]erik.brannstrom[/eluser]
You should look into many-to-many relationships. For example:

Code:

user    friend
----    ------
1        2
1        3
2        1
3        4

The numbers here are user id's from your user table, meaning that user 1 has added users 2 and 3 to their list, user 2 is also friends with user 1 and user 3 is friends with user 4.

This greatly improves the way you can insert and search for people connected!