Wanting to create a "buddy list" script...

kandieman101

Okay, so I am wanting to create a script, and the best way I can think is to call it a buddy list.

I have no problems with any aspect, except for saying who is who's buddy. What would be the best way to accomplish this? One person cannot be a friend of another, without the other being a friend of that person...

I can't quite think of how I would link them together. I first thought of like another table called friends which had USER_ID and FRIEND_ID... then just get all FRIEND_IDs for the current USER_ID... however, if user's 1 and 2 were friends.. in that table I would have
USER_ID = 1
FRIEND_ID = 2

USER_ID = 2
FRIEND_ID = 1

that seems rather redundant and surely there must be a better way. I think basically all IM programs accomplish this because I think each person has to be on each other's lists... so how can I cross reference the two user ids that they are FRIENDS OF EACH OTHER??

I hope I explained that well enough... what im thinking is hard to write down hehe..

Thanks in advance!

rpanning

Humm...I'm no database designer but, I would have three tables; USERS, PENDING, FRIENDS. You do need that middle table (FRIENDS) to link each user together.

USERS would have something like USR_ID, USR_NAME, etc. That would contain information about each person.

PENDING would have PEN_1 and PEN_2 containing the ID's of each user. PEN_1 would be someone requesting PEN_2 to be a friend. When PEN_2 accepts the "friend request" the ID's would be moved to FRIENDS.

FRIENDS would again just be FRE_1 and FRE_2. The only difference is that ID's in this field are actualy friends of each other.

Ex:

[b]USERS[/b]
USR_ID    USR_NAME
------------------
001       Joe
002       John
003       Jane
004       Ryan
005       David

[b]PENDING[/b]
PEN_1     PEN_2
---------------
001       005
005       002
004       003

[b]FRIENDS[/b]
FRE_1     FRE_2
---------------
004       005
001       004
004       002
003       002

Now to explain that..ok, you have the users shown in the first table. Now some users already have friends:
Joe has Ryan
John has Ryan and Jane
Jane has John
Ryan has Joe, John, and David
David has Ryan
You'll notice some names are listed above twice but only once in the table. That's because the relation goes both ways, you don't need to put the numbers in reverse order. Users who are friends with others are also the others friends, if that makes sense.

Now, some users arn't friends yet. One user wants to be friends with another user but the other user must first approve this. So:
Joe wants David
David wants John
Ryan wants Jane
Unlike FRIENDS, the order of ID's in the PENDING table makes a difference because PEN_1 requested friendship with PEN_2 so PEN_2 must be the one to accept the friendship. Once they do their ID's are move to FRIENDS.

That's how I would setup the database but you'd have to have PHP do some validation to make sure ID's are in proper places and no duplicates occur. Questions, let me know.

kandieman101

That is pretty much what I was thinking... the thing is, when I want to retrieve all friends of Ryan's (the 3 other Ids)

They are in different places... either FRE_1 or FRE_2...

So, for each of the 3 results I would have to check WHICH field contained the NON-RYAN id... to display their id. I just kind of thought that might be unecessary work to check every single row... is there a better way to accomplish this?

rpanning

You would setup the SQL query to get rows with id 004. Ex: SELECT FRE_1, FRE_2 FROM FRIENDS WHERE FRE_1=004 OR FRE_2=004

Then using PHP you could filter out the 004 ID's to leave just firends ID's. I do see what your saying, it is kind of more effort then what seems needed. Maybe someone else will know a better way.