People connected to other people

[Samvh]

Hello everyone,

This question is not related to an error, it is a question about finding the best possible way.

 

So I have a working "friend system". Basic example:

-------------------------

user_id    friend_id

3                6   

6                1

6                4

-------------------------

 

I'm trying to create some kind of "connected to", so for example User 3 (with ID 3) is friend to 6,

That makes user 3 also connected to 1 and 4 because (because 6 is already friends with 1 and 4).

 

I tried:

- $connected = mysql_query("SELECT * FROM friends WHERE user_id = $user_id");

- And then put the friend_id(s) in a fetch_array and in a variable($friend_id).

- So $friend_id holds all the friends from that user.

- Now I need to select everyting where user_id = $friend_id, then I know who is connected the the main user (ID 3)

 

But at this point I'm stuck, I hope I made my problem clear.

 

Thanks in advance

 

[teynon]

I think you should be careful, as this logic could result in very large results if you get to many users. Also, you have to make sure you don't loop back. Cause if friend 6 friends user_id 3, then you have a logical infinite loop.

 

So I think the best option is handling it in PHP, but unfortunately, you'd have to use a loop. There might be an option in MySQL but I am not aware of it.

 

$friendID=array();
$sql="However you select your table";
if ($query=@mysql_query($sql)) {
   while (@mysql_num_rows($query) > 0) {
     // Add your friends to the array and then update the sql query with the current friend id's.

    // Make sure you validate here whether the friend is already in the list. If it is and there are no other friends, break;
   }
}

[ebmigue]

You are in a good start.

 

What you are looking for is an operation that will compute the Transitive Closure of a set (http://en.wikipedia.org/wiki/Transitive_closure).

Specifically, Relational Transitive Closure.

 

AFAIK, SQL does NOT support this operation (transitive closure).

 

You can try re (http://www.reetudes.com). Usage would be:

 

$relationships = re::usevar('friends')->tclose()->toArray();
foreach($relationships as $tuple){
    //...echo $tuple->get('user_id') . ' ' . $tuple->get('friend_id')
}

 

However, if your table is quite large, admittedly, re may not process it efficiently. But there are other techniques to improve on the computation, say, perform paging on the results first.

 

Hope it helps.