PHP: Idea for friend-system?

I need help on making a friend-system.

I am thinking how to make so both users can see t开发者_JS百科hat they are friends together.

Should i just make a user1 field and user2 field, and then when displaying him/her´s friends, it should select where user1 ='$id' OR user2 = '$id' ?

Or should i make two rows each time people are being friends?

Smart way and example would be appreciated. Thank you.

I am storing in mysql database.

My thoughts is exactly in how should i list that who is friend with who. Lets say i use method 1) with user1 and user2 column, then i should have WHERE user1 or user2 is $id (users id) but can this work properly?

I just tried this and it shows the userid for user2 in user1´s friendslist, but in user2´s friendslist it just shows his own userid and not the user1s..

On a big projects with sharding you should always replicate data for each user.

For not so big project it's okay to keep one table with "initiator" and "accepter" fields for user_id. Don't forget to add indexes and "status" field for friendship

I'm assuming you're storing them in a database. A simple way would be to have a new table, lets call it "friendships" with two columns, user_1, user_2. If two users are friends, they should have a row in this table. This is an extremely simple way of implementing this, but it should work.

SELECT users.name, fr.name
FROM users, friends, users AS fr
WHERE users.user_id = ?
AND users.user_id = friends.user_id1
AND friends.user_id2 = fr.user_id
UNION 
SELECT users.name, fr.name
FROM users, friends, users AS fr
WHERE users.user_id = ?
AND users.user_id = friends.user_id2
AND friends.user_id1 = fr.user_id

This will allow you check against an intermediary table for your many-to-many relationship and not have to duplicate (inverse) rows.

CREATE TABLE `users` (
  `user_id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) NOT NULL,
  PRIMARY KEY (`user_id`),
);

CREATE TABLE `friends` (
  `user_id1` bigint(20) unsigned NOT NULL,
  `user_id2` bigint(20) unsigned NOT NULL,
  PRIMARY KEY (`user_id1`,`user_id2`)
);

Some good answers here, If I had to do this I would do it one way, this way you can determine if the friendship has been confirmed and also ensure the friendship is mutual, so for example

Lets assume status has two values

0  = Unconfirmed
10 = Confirmed

Using simple tables with status for acceptance levels

CREATE TABLE `users` (
    `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
    `name` varchar(50) NOT NULL,
    PRIMARY KEY (`user_id`),
);

CREATE TABLE `friend` (
    `user_id`   bigint(20) unsigned NOT NULL,
    `friend_id` bigint(20) unsigned NOT NULL,
    `status`    int(11) unsigned NOT NULL,
    PRIMARY KEY (`user_id`,`friend_id`)
);

INSERT INTO `users` (`id`, `name`) VALUES
    (1, 'Jack'),
    (2, 'John');

Jack would like to be friends with John, so you would create a relationship between the two only one way:

INSERT INTO `friend` (`user_id`, `friend_id`, `status`) VALUES (1, 2, 0);

Now you can query the database to find Jacks friends or Jacks requests and Johns friends or Johns requests using simple queries

To find Jacks unconfirmed friends you would use something like

SELECT users.* FROM users JOIN friend ON users.id = friend.user_id WHERE friend.friend_id = 1 AND friend.status = 0

To find Jacks confirmed friends you would use something like

SELECT users.* FROM users JOIN friend ON users.id = friend.user_id WHERE friend.friend_id = 1 AND friend.status = 10

To find Jacks any friend requests you would use something like

SELECT users.* FROM users JOIN friend ON users.id = friend.user_id WHERE friend.friend_id = 1

When someone makes a confirmation of friendship you would perform 2 queries, one for updating the record and one as a reverse confirmation

UPDATE friend SET status = 10 WHERE user_id = 1 AND friend_id = 2;
INSERT INTO `friend` (`user_id`, `friend_id`, `status`) VALUES (2, 1, 10);

On a different note, I would also use a Graph database for complex relationship queries whilst maintaining a firm copy in the MySQL database

Graph teaser for friends of friends to build relationships

MATCH (Person { id: 1 })-[:knows*2..2]->(friend_of_friend) WHERE NOT (Person)-[:knows]->(friend_of_friend) AND NOT friend_of_friend.id = 1 RETURN friend_of_friend.id, friend_of_friend.name LIMIT 10