PHP - Add users as friends

Question

I have a social website, you can add friends. The way it works is like this:

• click add friend
• add the user who clicked into a database row, like this in the columns [bob][fred][123123]
• fred accepts it with the secret in a verification link, via email.
• this then puts another row in the database the other way round, like this [fred][bob][123123]

I can then determine if they are friends or not and if they have been accepted.

However, this surely isn't the correct way. I am now very unsure of a way of which could be better and run more smoothly.

Answer 1

There is really no right or wrong way, it is what ever you decide to use, at the end of the day it is your code, so whatever you choose and works for you.

However, you are quite correct, entering two rows would be a very heavy overhead and use extra space for seemingly no good reason. A lot more simply you could set another column in your DB:

user_1 | user_2 | accept_code | accepted

user_1 requests to add user_2 as a friend - you set an accept_code creating an entry in the DB. Set your DB structure to set the accepted column to define as false. Then when the row is first created the users are not currently friends.

Using your example: bob requests fred as a friend. You DB would now look like this:

bob | fred | 123123 | false

When user_2 enters the accept_code, then change accepted to true.

bob | fred | 123123 | true

This way, one query will tell you if the two users are friends, instead of two queries to see if you have two matching DB entries.

So for example, bob has added fred, joe and alex as friends, fred and alex have accepted bob as a friend, but joe has not. You DB would look like this:

user_1 | user_2 | accept_code | accepted

bob | fred | 123123 | true

bob | joe | 321321 | false

bob | alex | 789789 | true

So for example, a psuedo select maybe, find all friends for bob:

SELECT user_2 FROM relationships WHERE user_1="bob" AND accepted="true"

The result would be:

fred alex

UPDATE as per the comments:

DB Structure:

user_1 | user_2 | accept_code | accepted

bob | fred | 123 | true

bob | alex | 123 | true

bob | joe | 123 | false

ste | bob | 123 | true

joe | alex | 123 | true

Select statement:

SELECT * FROM relationships WHERE accepted = 'true' AND (user_1 = 'current_user' OR user_2 = 'current_user');

Example 1 - bob logs in, he has requested friends and been requested as a friend:

SELECT * FROM relationships WHERE accepted = 'true' AND (user_1 = 'bob' OR user_2 = 'bob');

The result:

bob | fred | 123 | accepted

bob | alex | 123 | accepted

ste | bob | 123 | accepted

Example 2 - alex logs in, he has never requested and friends but has been requested as a friend:

SELECT * FROM relationships WHERE accepted = 'true' AND (user_1 = 'alex' OR user_2 = 'alex');

The result:

bob | alex | 123 | accepted

joe | alex | 123 | accepted

Example 3 - joe logs in, he has requested a friend and has declined a friend:

SELECT * FROM relationships WHERE accepted = 'true' AND (user_1 = 'joe' OR user_2 = 'joe');

The result:

joe | alex | 123 | accepted