Recusive Search

[Marco Schierhorn]

Hey MySQL Specialists, :-)

i´ve got the following problem.
We want to search a table for friends of a specified member.
After that we want to search the friends of our friends and so on.
Until the 4th degree.
This is the table :

mem_id bigint(20)
frd_id bigint(20)

e.g. we´ve the following values :

mem_id frd_id
1 2
1 3
1 5
2 1
2 3
2 4
2 6
3 1
3 2

The first statement should show our friends ( 2,3,5 ), the second all friends
of our friends which are not in my network ( 4,6 ) ( 2nd degree )
The third statement should show the friends of these ( 3rd degree ).

We wrote a recursive function in php :

function return_countfriensdegrees($member){

// level 1 ermitteln
$members_lev1d = gForum::return_friends($member);
$members_lev1 = array_unique($members_lev1d);

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

// 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($members_lev1), "2" => sizeof($members_lev2), "3" => sizeof($members_lev3), "4" => sizeof($members_lev4));
return $retarray;
}

It works fine, but i think there will be a better solution.
I remember there was the possibilty to do this with minus in Oracle.
So i´ve tested the 2nd degree with a mySQL Statement like this :

SELECT COUNT( DISTINCT (n2.frd_id) )
FROM network n1, network n2
LEFT JOIN network n3 ON ( n2.frd_id = n3.frd_id AND n1.mem_id = n3.mem_id )
WHERE n1.mem_id =1
AND n1.frd_id = n2.mem_id
AND n3.frd_id IS NULL

It works as well, but i think there also will be a better solution...

I would really appreciate if someone can help me.

Cheers

Marco

[e-mail]



Edited 1 time(s). Last edit at 09/29/2005 04:26PM by Marco Schierhorn.