Friend of a Friend in PHP/Mysql

Friend of a friend in PHP/MySQL?

The answer is that they aren't doing selects on a friend table, they are most likely using a de-normalized news-event table. We implemented a news-feed similar to Facebooks on DoInk.com, here's how we did it:

There is the notion of a "NewsEvent" it has a type, an initiator (a user id) and a target user (also a user id). (You can also have additional column(s) for other properties relevant to the event, or join them in)

When a user posts something on another users wall we generate an event like this:

INSERT INTO events VALUES (wall_post_event, user1, user1)

When viewing user1's profile, you'd select for all events where user1 is either the initiator or the target. That is how you display the profile feed. (You can get fancy and filter out events depending on your privacy model. You may consider doing this in memory for performance reasons)

Example:

SELECT * FROM events WHERE initiator = user1 or target = user1 //to see their profile feed

SELECT * FROM events WHERE initiator IN (your set of friend ids) //to see your newsfeed

When you want to see the newsfeed for all events relative to your friends you might do a query selecting for all events where the initiator is in your set of friends.

Avoid implementations with sub-selects, depending on the complexity, they will not scale.

Friend system in PHP/MySQL?

SELECT DISTINCT
a.username,
a.profile_img
FROM
users a
WHERE
a.id in (SELECT user_a FROM friends WHERE user_a = $userid OR user_b = $userID)
and a.id <> $userid

UNION

SELECT
b.username,
b.profile_img
FROM
users b
WHERE
b.id in (SELECT user_b FROM friends WHERE user_a = $userid OR user_b = $userID)
and b.id <> $userid

Check that two users are friends together in php/mysql

You'd do it by using OR.

SELECT 1 FROM friends WHERE (requestuserid = :id1 AND targetuserid = :id2) OR (requestuserid = :id2 AND targetuserid = :id1)

This query will return an empty set on failure, and a 1 on success.

Check Mutual Friends from my friend using MySql

Use self joins and aggregation:

SELECT u.user_id, u.name, 
       COUNT(f3.friend_id) mutual_friends
FROM friends f1
INNER JOIN friends f2 ON f2.user_id = f1.friend_id
INNER JOIN users u ON u.user_id = f2.user_id
LEFT JOIN friends f3 ON f3.user_id = f1.user_id AND f3.friend_id = f2.friend_id
WHERE f1.user_id = ?
GROUP BY u.user_id, u.name;

Replace ? with user id that you want.

See the demo.

Search by Mutual Friends Count - Friend System Mysql PHP

This query will take the reciprocity of relationships into account, so it doesn't matter if the relationship goes "From A to B" or "From B to A", it will still return the expected result. So given tables like this:

CREATE TABLE people
    (`id` int, `name` varchar(1))
;

INSERT INTO people
    (`id`, `name`)
VALUES
    (1, 'A'),
    (2, 'B'),
    (3, 'C'),
    (4, 'D'),
    (5, 'E'),
    (6, 'F')
;

CREATE TABLE friends
    (`id` int, `personId1` int, `personId2` int)
;

INSERT INTO friends
    (`id`, `personId1`, `personId2`)
VALUES
    (1, 1, 2),
    (2, 3, 1),
    (3, 2, 4),
    (4, 5, 2),
    (5, 3, 4)
;

I believe this is set up as you described: A and B are friends, A and C are friends (notice the inverted relationship), B and D are friends, E and B are friends (another inverted relationship), and C and D are friends.

Assume the id of the person you want is in @personId:

SELECT StrangerId, COUNT(MutualFriendId) AS TotalMutualFriends
FROM
(SELECT
  CASE WHEN f.personId2 = mf.friendId THEN f.personId1 ELSE f.personId2 END AS StrangerId,
  CASE WHEN f.personId1 = mf.friendId THEN f.personId1 ELSE f.personId2 END AS MutualFriendId
FROM
(SELECT 
  CASE 
    WHEN personId1 = @personId THEN personId2 
    ELSE personId1 
  END AS friendId 
FROM friends 
WHERE personId1 = @personId OR personId2 = @personId) AS mf
INNER JOIN friends f
ON (personId1 != @personId AND personId2 = mf.friendId) 
  OR (personId1 = mf.friendId AND personId2 != @personId)
 ) AS totals
 GROUP BY StrangerId
 ORDER BY TotalMutualFriends DESC;

Results for @personId = 1 are:

StrangerId  TotalMutualFriends
4           2
5           1

And here is a SQLFiddle to demonstrate (I couldn't get it to allow me to set up a variable, so there is a 1 in its place).

PHP - Add users as friends

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

Mysql friends online display

<?php
    $friends = array(1,5,16); // Array of friends
    $friendIDs = implode(',', $friends); // Turns array into string for SQL select statement

    // Gets only friends info from DB
    $sql = "
        SELECT date, ip, name, user_id
        FROM users_online
        WHERE user_id IN (".$friendIDs.")";
?>

---

Related Topics