[RESOLVED] Friend System Structure

[snapbackz]

Hi,

I am trying to create a friend system for my users. I have a question but first, here is my setup for the friends (link) table:

ID | Friend1_id | Friend2_id | Accepted

Friend1 is the person making the request and Friend2 is the person who has been requested. This works fine and dandy until I need to show a friends list on any users profile. Mainly because sometimes you would be the person who requested and sometimes you would be the person who received a request.

What i mean is say I wanted to get user X's friends and JOIN it with the user table..

SELECT * FROM FriendTable LEFT JOIN (`users`) ON ( [B][COLOR="Red"]*something here*[/COLOR][/B] ) WHERE (Friend1_ID = 'user_id' AND Accepted = 'y') OR (Friend2_ID = 'user_id' AND Accepted = 'y')

The something here part poses a problem because I don't know which is the users friend and which is well, the user. Sometimes it would pull their own id.

I thought of adding another table instead of the column accepted but I would have to enter a 2 relationships for each set of users.

I'm probably making this more complicated than it is. Any suggestions?

Thank you

[tekky]

Why not simply insert 2 entries per "friend"
userid1, userid2, true
userid2, userid1, true

then ONLY query against col1 to find userid1's friends
and if you want userid2's friends, query for his id in col1...

As for your join...

SELECT * From FriendTable AS f LEFT JOIN (users AS u) ON (f.col1 == u.col1).....

I was gonna test but my SQL server went AWOL it seems... 🙁

[troybtj]

something here would be, I'm assuming:

(Friends.ID = Users.uid)

You might need to put the OR condition in the something here, basically swapping the query right side to the join, and then the equal IDs for the "where".

[snapbackz]

Thank you for the responses. Tekky, that's the same conclusion I came too. This would allow for "top friends" on each users profile as well. =]

Thank you