php friending system

If someone can help me with this issue I have I'll appreciate it SO MUCH!

I'm making a website where I want registered users to be able to add each other as friends.

The person must "invite" another registered user (on the database). The other person will receive a notification of some sort. If the person ignores the notification, nothing happens and when he/she approves, they are friends.

Now I have a idea with the table I'm going to use:

+----------------------------------------+
|               Requests                 |
+----------------------------+-----------+
| id | user_from |  user_to  |   status  |  
+----+-----------+-----------+-----------+
|    |           |           |           |
|    |           |           |           |
|    |           |           |           |
|    |           |           |           |
+----------------------------+-----------+

As I said this is only an idea I want to use but I don't know what the code will be in the php w开发者_如何转开发hen I have a link to add a person as a friend.

I think the admin to approve of the request sent and when the user approves or not. I don't know how that table will look.

And I thought about using INNER JOIN to "join" the two users as friends. BUT again, not completely sure how to go about that. Will this come in my php? Lets say friends.php?

SELECT name, email FROM users

INNER JOIN friends ON users.id = friends.user.id WHERE users.id = 1

As I said, I have a good idea of what to do but when it comes to coding it, I'm not completely sure how to do that...

Thanks!

---

You should have a relationships table that simply associates user.id's.

+-----------------------------------------+
|               relationships             |
+-----------------------------------------+
| id | user_id1  |  user_id2   |  status  |
+----+-----------+-------------+----------+
|    |           |             |          |
|    |           |             |          |
|    |           |             |          |
|    |           |             |          |
+------------------------------+----------+

Where user_id1 and user_id2 are both user.id. Note: I'd probably make status be an enum, with something like 'pending, declined, friend, enemy'. I initially thought of using a bool, but what if there is some other desired status later on? Like maybe you keep track of broken friendships, or some other kind of relation.

Then you simply do a multiple where clause to determine friends: WHERE (user_id=:id OR friend_id=:id) AND status='friend'

---

Your implementation can vary - There are multiple ways to provide the functionality you desire, namely:

1. User A requests to be User B's friend
2. User B receives a notification of the friend request, and can accept, decline, or ignore
3. Whenever User B accepts the request, they are friends

Since your questions is vague, I can only offer suggestions since there aren't any specific questions posed.

SQL Schema - Your table looks appropriate for friend requests. You may want to clarify the table name, as requests can be a broad topic. You do not necessarily need an id column, if you define a unique index on the user_from, user_to columns (which would prevent duplicate friend requests to the same person). Again, this is your choice, and no one answer is necessarily "correct". For the status field, consider using an ENUM of the valid states of the request, e.g. ENUM("PENDING", "DECLINED").

As far as notifications, the best way to do this is to insert a notification into a notifications table when a user initiates a friend request, instead of trying to derive a notification by checking for new friend requests. The first is more extensible, and allows you to raise notifications for a variety of reasons.

Finally, once the friend request succeeds, you can do one of two things:

1. Remove the request from the friend requests table, and insert into a friends table
2. Mark the request as accepted

Again, your choice on implementation here. Option #1 allows you to easily query out friends but yields an extra table and (possibly) two rows for each friendship, if that's how you choose to implement it. Option #2 requires you to query the requests table for requests that are completed, making your query slightly more complicated and IMO a bit less intuitive, since Friends and Friend Requests aren't necessarily related. In addition, you can add fields in the Friends table, such as a date that the friendship started, that wouldn't make sense in the Friend Requests table.

Queries - When you need to lookup a user's friends, you can do something similar to the following, depending on what you decide on for your SQL schema:

SELECT * FROM friends WHERE user_id = $user_id
SELECT * FROM friends WHERE user_id = $user_id OR friend_id = $user_id
SELECT * FROM friend_requests WHERE (user_from = $user_id OR user_to = $user_id) AND status = "COMPLETED"