Jump to content

Friends list - need opinions.


Recommended Posts

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?

Link to comment
Share on other sites

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,


Link to comment
Share on other sites

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








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.





Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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'];
$friend = $row['user_b'];

Link to comment
Share on other sites

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...

Link to comment
Share on other sites

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. :D)


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.

Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.