setting up a friend list in mysql

I want to make a friends list in my online game. I am not sure how to set it up or where to start. The add frien开发者_运维百科ds and accept friends part I can handle, but I don't know how to set it up in mysql and php. A list of users (friends) connected to each user or something?

If your friendship relationship is symmetrical, you can either store each pair in a separate record:

friend1 friend2
A       B
B       A
A       C
B       D
C       B
D       B

and query all B's friends like that:

SELECT  friend2
FROM    friends
WHERE   friend1 = 'B'

or store the user with the least id in the first field and that with the greatest id in the second one:

friend1  friend2
A        B
A        C
B        D

and query B's friends like that:

SELECT  friend1
FROM    friends
WHERE   friend2 = 'B'
UNION ALL
SELECT  friend2
FROM    friends
WHERE   friend1 = 'B'

The first option is a little bit more efficient in MySQL, and this is the only option if your friendship relationship is not symmetrical (like on LiveJournal)

See this article:

• Selecting friends

A friendship is essentially a mutual relationship between two people. In database terms it's a many-to-many relationship between two users.

So what you need is a linking table that holds references to two users by ID.

Example

Users table

ID    Username
1     Bob
2     Jim
3     Alice

Friends table

user1    user2
1        2
2        3

This would make Bob friends Jim and Jim friends with Alice.

Check out the answers in this other post here on stackoverflow...some simple but great explanations to accomplishing what you need.

Facebook database design?

An alternative would be to set column A as follow: $checkfriend = (($friend1 $friend2) || ($friend2 $friend1))

Column B as follow: (friendship accepted or friendship rejected) based on user's choice

Basically have both user1 and user2 in the same column.