[sandeep.kumar]

Hi All,

I need to implement "Friend of a Friend" problem with minimal costs using either MySQL5 or PHP/MySQL. I wonder if there is an implementation that would just allow to solve this problem with minimal costs
The description is as below:-

I want to build a "social network" extension and try to find the best way to implement a Friend of a Friend connection path. (Facebook has something like that I think)

e.g. We both have the same friend, but don't know each other. The algorithm should give me a path from me, over our same friend to you. (Me - Friend - You)

But it should also find connections, that have a bigger distance, e.g. (Me - Friend1 - Friend2 - Friend3 - You) but not exceeding this situation - (Me - Friend1 - Friend2 - Friend3 - Friend4 - You)

I stored user relations in database as I (user_id|friend_id = me|friend and friend|me)

Any help would be really grateful

[Nico]

I'm moving this to the database section.

[sandeep.kumar]

Why r u moving it to Database section it may be stay here.

[Nico]

You posted this in the "New Members & Introductions" section.

You might be a new member, but your question seems more database related...

[sandeep.kumar]

ok and thanks for your help.

[job0107]

If you have a table with two fields (user,friends), you could use two queries.

PHP Code:

$query1="SELECT friends FROM table WHERE user='you'";
$results=mysql_query($query1);
while($row=musql_fetch_assoc($results))
{
 $array1[]=$row["friends"];
 }
$query2="SELECT friends FROM table WHERE user='me'";
$results=mysql_query($query1);
while($row=musql_fetch_assoc($results))
{
 $array2[]=$row["friends"];
 } 

Then use the array_search() function to see if value from array1 exists in array2 and display the results.

[UnrealEd]

I'm not sure, but maybe you can do it all in one query:

SELECT
  friends
FROM
  mytable AS t1
INNER JOIN
  mytable AS t2
ON
  t1.friends = t2.friends
WHERE
  t1.user='me'
AND
  t2.user='you' 

In theory it should work, but I haven't tested it

[job0107]

Originally Posted by UnrealEd

I'm not sure, but maybe you can do it all in one query:

SELECT
  friends
FROM
  mytable AS t1
INNER JOIN
  mytable AS t2
ON
  t1.friends = t2.friends
WHERE
  t1.user='me'
AND
  t2.user='you' 

In theory it should work, but I haven't tested it

Your query is very well thought out, and it will work this way:

PHP Code:

<?php
$sql="SELECT * FROM mytable AS t1 INNER JOIN mytable AS t2 ON t1.friends = t2.friends WHERE t1.user='me' AND t2.user='you' LIMIT 4";
$results=mysql_query($sql)or die(mysql_error());
echo "Me - ";
while($row=mysql_fetch_assoc($results))
{
 echo $row["friends"]." - ";
 }
echo "You";
?>

And this way:

PHP Code:

$sql="SELECT * FROM mytable AS t1 JOIN mytable AS t2 ON t1.friends = t2.friends WHERE t1.user='me' AND t2.user='you' LIMIT 4";
$results=mysql_query($sql)or die(mysql_error());
echo "Me - ";
while($row=mysql_fetch_assoc($results))
{
 echo $row["friends"]." - ";
 }
echo "You"; 

[sandeep.kumar]

If i have to search three or four level friends than will these query work fine?

I have a large database of Friend's connections than these query will take a lot of time.

I need to save time also.

[job0107]

Originally Posted by sandeep.kumar

If i have to search three or four level friends than will these query work fine?

I have a large database of Friend's connections than these query will take a lot of time.

I need to save time also.

It all depends on how you have your database setup.