Degrees in network between members/friends

[ESV Media GmbH]

Hey everybody,

i´m becoming desperate about the following problem.
We´ve an online-community / network.
There´s a network table ( sql create below ), in which we store the
relationships between the member.
I need a SQL-statement or a PHP-Function to calculate the 2nd, 3rd and
4th degrees between the members.
So, the friends of my friends, the friends of them and so on.
I would really appreciate any help and would be so happy if you can help
me with this problem :

Here is the function i´ve right now :


function getFriends($uid,$userself=0,$test=0){
$uid = mysql_real_escape_string(strip_tags($uid));
// Liefert alle "ersten grad Freunde" eines Members zurück


$res = mysql_query(" (
SELECT bid AS frd_id FROM network n
WHERE ( n.mid IN (".$uid.") AND n.bid NOT IN (".$uid.",$userself) )
)
UNION
(
SELECT mid AS frd_id FROM network n
WHERE ( n.mid NOT IN (".$uid.",$userself) AND n.bid IN (".$uid.") )
)",_CON);
$this->mySQLOptimizer("get friends");

if ($test==1)
{
die(" (
SELECT bid AS frd_id FROM network n
WHERE ( n.mid IN (".$uid.") AND n.bid NOT IN (".$uid.",$userself) )
)
UNION
(
SELECT mid AS frd_id FROM network n
WHERE ( n.mid NOT IN (".$uid.",$userself) AND n.bid IN (".$uid.") )
)");
}

$friends = array();
if ( mysql_num_rows($res) >= 1 )
{
while ($data = mysql_fetch_array($res))
$friends[] = $data["frd_id"];
}
else
{
$friends=0;
}
return $friends;
}

function getFriensdegrees($uid){
$uid = mysql_real_escape_string(strip_tags($uid));
// level 1 ermitteln

$retarry[1] = $this->getFriends($uid);
if (is_array($retarry[1]))
{
// level 2 ermitteln
$retarry[2] = $this->getFriends(explode(',',$retarry[1]),$uid,1);

if (is_array($retarry[2]))
{
$retarry[3] = $this->getFriends(explode(',',$retarry[2]),$uid);
}
else
{
$retarry[3] = "0";
}

if (is_array($retarry[3]))
{
$retarry[4] = $this->getFriends(explode(',',$retarry[3]),$uid);
}
else
{
$retarry[4] = "0";
}
$returnarray = array("1" => $retarry[1], "2" => $retarry[2], "3" =>
$retarry[3], "4" => $retarry[4]);
}
else
{
$returnarray = "";
}
return $returnarray;
}

function countfriensdegrees($uid){
$uid = mysql_real_escape_string(strip_tags($uid));
// level 1 ermitteln
$retarry[1] = $this->getFriends($uid);
if (count($retarry[1]) >= 1 && is_array($retarry[1]))
{
// level 2 ermitteln
$retarry[2] = $this->getFriends(implode(",",$retarry[1]),$uid);

if (is_array($retarry[2]))
{
$retarry[3] = $this->getFriends(implode(",",$retarry[2]),$uid);
}
else
{
$retarry[3] = "0";
}

if (is_array($retarry[3]))
{
$retarry[4] = $this->getFriends(implode(",",$retarry[3]),$uid);
}
else
{
$retarry[4] = "0";
}


/*
// level 3 ermitteln
foreach($members_lev2 as $value){
$res = gForum::return_friends($value);
foreach($res as $val) $members_lev3d[] = $val;
unset($res);
}
$members_lev3 = array_unique($members_lev3d);
$members_lev3 = array_diff($members_lev3, $members_lev2);
$members_lev3 = array_diff($members_lev3, $members_lev1);
$members_lev3 = array_diff($members_lev3, array("0" => $member));

// level 4 ermitteln
foreach($members_lev3 as $value){
$res = gForum::return_friends($value);
foreach($res as $val) $members_lev4d[] = $val;
unset($res);
}
$members_lev4 = array_unique($members_lev4d);
$members_lev4 = array_diff($members_lev4, $members_lev3);
$members_lev4 = array_diff($members_lev4, $members_lev2);
$members_lev4 = array_diff($members_lev4, $members_lev1);
$members_lev4 = array_diff($members_lev4, array("0" => $member));
*/
$retarray = array("1" => sizeof($retarry[1]), "2" => sizeof($retarry[2]));
}
else
{
$retarray = array("1" => 0, "2" => 0);
}
return $retarray;
}



Network Table :

bid is the id of the person i´ve connected.

DROP TABLE IF EXISTS `db80049_36`.`network`;
CREATE TABLE `db80049_36`.`network` (
`id` int(11) NOT NULL auto_increment,
`mid` int(11) NOT NULL default '0',
`bid` int(11) NOT NULL default '0',
`reason` longtext NOT NULL,
`action` int(1) NOT NULL default '0',
`sent` datetime NOT NULL default '0000-00-00 00:00:00',
`action_on` datetime NOT NULL default '0000-00-00 00:00:00',
`reason_decline` longtext NOT NULL,
PRIMARY KEY (`id`),
KEY `mid` (`mid`),
KEY `bid` (`bid`),
KEY `accepted` (`action`),
KEY `mid_bid_together` (`mid`,`bid`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:    http://lists.mysql.com/mysql?unsub=lists@...