Clever Geek Handbook

How can I get friends of friends using mysql and PHP

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

For example: the database structure looks 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 can get friends

SELECT * FROM tbl_friendlist WHERE mem_id = 1.

Using this, I can get a list of my friends. Now how can I get friends of friends

+2


source to share


6 answers


i would do a subquery too (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).
+4


source


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

This is how you get a list of all your friends

SELECT friend_id
FROM tbl_friendlist
WHERE f.mem_id = 1

And then you just wrap this 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 can also add additional filters there, for example:

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 that you will not get duplicates and you will end up on your friends list of friends.

+4


source


If you need to find a way without subqueries, you can try to do this by joining the table 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;
+3


source


Nickf's solution will work well, but I'll personally do it in 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;
+3


source


Hmm, I think something like this might 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 friend ids of the friends as a separate comma value in the f_of_f column.

+2


source


and friends of friends of friends:

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

Is it correct?

0


source






More articles:


All Articles