Friends list - need opinions.

[waynew]

I was looking through the DB setup of SMF when I realized that they didn't have a table dedicated to handling the user's buddies list. They instead have one column inside the members table dedicated to keeping a list of all friends. Is this a go economic way to go about things? Inserting a list of user ids into one column, with each of them separated by commas so that you can explode them upon extraction? Can anyone think of a major drawback to doing it this way?

[cmgmyr]

I think you need to look at this a little differently. In my opinion a "buddy list" is a 1 way list, this means that I can have you on my list but you don't need to have me on your list (think of AIM). But a friend's list is a 2 way list where both people need to agree to be friends (think of MySpace). I have a friend's list on one of my sites and I have a table dedicated to it. It makes it very easy to search/sort through everything and match people up on either profile (friend 1 or friend 2).

 

hope this helped,

-Chris

[waynew]

At the moment, I have a table dedicated to a friends list.

 

FRIENDS:

friendship_id

user_a

user_b

confirmed

 

When a user sends a friend request, his or her user id is inserted into the column user_a and the person that he or she is sending a request to is inserted into user_b, while the TINYINT column confirmed has a default value of 0. When the request is accepted, the confirmed column is updated to 1.

 

Problem is, the query to get a users friends ends up being pretty complex (for me anyway). Bear in mind that the below is only an example. The real query takes data from multiple tables.

 

SELECT user_a, user_b FROM friends WHERE user_a = '$user_id' OR user_b = '$user_id' AND confirmed = 1

 

Problem is that this query returns the users id. So I need to come up with a query that does not return user_a if user_a = '$user_id'... and vice versa. Otherwise I'd be extracting the same values over and over again.

 

 

 

 

[waynew]

Bump. For great justice.

[waynew]

Bump again.

[Brian W]

I don't think that that is any slower/faster or worse than using one field with comma deliminated numbers. Actually, think the way you have it is easier to manage imho.

 

possible logic.

$id is the id of the user that you are viewing the profile of

if($id = $row['user_a']) {
  $u_id = $row['user_b']; 
} else {
  $u_id = $row['user_a']; }

That should pick which id (a or b) to use as the friend's Id for that result.

probably better ways to do that.

[waynew]

Hi Brian, thanks for replying. I had something like that going on in my last script. Maybe I should explain the problem a little bit better.

 

Say for example that a user is viewing his or her friends list.

 

$user_id = $_SESSION['user_id']; //user id of user who is checking his or her friend list

$result = mysql_query("SELECT * FROM friends WHERE (user_a = '$user_id' OR user_b = '$user_id') AND confirmed = 1");

 

This will return the users id who is checking his or her friend list, each time. Basically, the perfect query would:

 

Select user_b's details if user_a = $user_id and select user_a's details if user_b = $user_id

[cmgmyr]

If you use this:

$user_id = 1;
SELECT * FROM friends WHERE (user_a = '$user_id' OR user_b = '$user_id') AND confirmed = 1

 

Then for each row you do something like this:

if($user_id != $row['user_a']){
$friend = $row['user_a'];
}else{
$friend = $row['user_b'];
}

[waynew]

Sh*t. Misunderstood Brian W's suggestion. Thanks guys.

[waynew]

[code=php:0]
if($user_id != $row['user_a']){
$friend = $row['user_a'];
}else{
$friend = $row['user_b'];
}

 

But wouldn't that mean that even if user_a is the friend, that user_b is also being returned from the db?

[Brian W]

what i was saying and cmgmyr continued is for that user's profile or whatnot. If a user goes to another user's profile and they want to see THAT user's friends, you need to run the query then figure out which of the two fields are NOT the user's ID that you are viewing the profile of.

This is like going to John Doe's profile on myspace. or you already knew that?

so maybe you meant and I misunderstood that the query returns both values. if so, yes it returns both but you only use one ($friend) to determine the ID of the friend which you use is a separate query to get there name, pic, ect...

[DarkWater]

1) Don't ever store a list of values separated with a delimiter in a single column, or I'll go to your house and create a hostage situation.  (Being creative today. )

 

2) I had something like this set up on a little user system I made one day out of boredom.  You have to do a join with the users table:

 

SELECT u.* FROM friends AS f INNER JOIN users AS u ON f.friend_id = u.user_id WHERE f.user_id = '{$_SESSION['user_id']}' AND f.accepted = 1

 

That query fetches any given user's friends.  It was copied and pasted from my PHP file, so I know it works.