How to create Facebook like friends system in php with mysql

Question

I want to create a friends system like facebook, well quite weird than that. When a user adds someone, the someone will be notified, if agree then they'll be mutual friends(in this case, meaning both parties are friends, literally) or if the someone leaves the request unresponded, they'll become fan of the someone. The someone reject the request, they'll no longer fan nor friend.

How can do this in PHP with MYSQL. Here is my initial planning, somehow im not sure it'll be the best way to do it.

MYSQL TABLE

user_id (request made by) | friend_id (request point towards) | status
--------------------------------
123     | 452       | 'waiting'
525     | 123       | 'waiting'

Meaning user 123 have 1 friend request from 525, and made 1 friend request to 452. The problem here is how Im going to get the friend list of user 123? if user 123 accept 525's request and user 452 accept 123's request. And also how to get the fans list by MYSQL QUERY..and how to get friends of friend?

Answer 1

Friend list of user 123:

SELECT u.* FROM users u INNER JOIN friend_requests f ON f.user_id = u.id WHERE f.user_id = 123 AND status = 'accepted';

User 123 is fan of these users:

SELECT u.* FROM users u INNER JOIN friend_requests f ON f.user_id = u.id WHERE f.friend_id = 123 AND status = 'waiting';

Don't forget to add index on the status column.

Also, I'm not sure a table like this is ideal for this.

EDIT:

I would probably choose a schema like this:

friend_request
    request_from (foreign key to users.user_id)
    request_to (foreign key to users.user_id)
    resolved (boolean 1 or 0, default is 0)

friend_xref
    friend (foreign key to users.user_id)
    is_friend_with (foreign key to users_user_id)

fan_xref
    user (foreign key to users.user_id)
    is_fan_of (foreign key to users.user_id)

When somebody makes a friend request, add new row to friend_request table. When the request recipient chooses to:

• to accept the request: add new row to friend_xref and set resolved to 1 in the friend_request table
• to deny the request: add new row to fan_xref and set resolved to 1 in the friend_request table

But it would be better to ask this under mysql, database-design or some similar tag to get best answers.

Answer 2

Probably best to create a 'status' table then use a foreign key in the status column as well :)