How shall i get my friends of friends using mysql and PHP

Question

Currently I am in need to find the friends of friends in my application using PHP and MYSQL

For example : Database structure is like this. Table Name : tbl_friendlist

Ident   mem_id  friend_id  


1         2     1      

2         1     2  

3         1     3  

1         4     1     


5         1     8

How can i achieve this

I am able to get the Friends by

SELECT * FROM tbl_friendlist WHERE mem_id = 1.

By using this i am able to get my friends list. Now how can i get the friends of friends

Thanks in advance!

Answer 1

So, breaking into steps -- you want to find all your friends, and then you want to find all of their friends. These are the two parts of your query (and subquery):

This is how you'd get a list of all your friends

SELECT friend_id
FROM tbl_friendlist
WHERE f.mem_id = 1

And so then you just wrap that in the second part:

SELECT f.friend_id
FROM tbl_friendlist f
WHERE f.mem_id IN (
    SELECT f2.friend_id
    FROM tbl_friendlist f2
    WHERE f2.mem_id = 1
)

You might also want to add some extra filters in there, eg:

SELECT DISTINCT f.friend_id
FROM tbl_friendlist f
WHERE f.mem_id IN (
    SELECT f2.friend_id
    FROM tbl_friendlist f2
    WHERE f2.mem_id = 1
) AND f.friend_id <> 1

This means you won't get duplicates, and you find yourself in your list of friends' friends.

Answer 2

i would do a subquery as well (my SQL is really rusty, but the query will look something like this)

SELECT * from tbl_friendlist where mem_id in (SELECT friend_id FROM tbl_friendlist WHERE mem_id = 1).

Answer 3

If you need to find a way without subqueries you can try to do this by joining the table ON itself.

Idea, untested:

SELECT a.mem_id, b.mem_id AS secundaryfriends FROM tbl_friendlist AS a
JOIN tbl_friendlist AS b ON a.friend_id = mem_id;

Answer 4

Hmm, I think something like this may work:

SELECT 
   f1.friend_id, group_concat( f2.friend_id ) as f_of_f
FROM 
   tbl_friendlist f1
   LEFT JOIN 
   tbl_friendlist f2 ON ( f1.friend_id = f2.mem_id )
WHERE 
   f1.mem_id = 1
GROUP BY 
   f2.mem_id

This will give you the ids of the friends of friends as a comma separate value in f_of_f column.

Answer 5

The solution by nickf will work well, but I'd personally do this in a JOIN:

SELECT
    DISTINCT fof.friend_id
FROM
    tbl_friendlist f
    JOIN tbl_friendlist fof ON fof.mem_id=f.friend_id
WHERE
    f.mem_id = 1 AND fof.friend_id != 1;