query db to return all related rows to a given row and all related to those and so on and so on....

[monomaniac21]

hi all

On a networking site i am working on each user has their own id and the
id of another person who they are linked to. what i want to do is to be
able to pull up a list of everyone linked to one person and all the ppl
linked to them. So the query would work something like get id of linked
get linked row get id of linked to this person get of person get id of
linked to person.... this needs to go on until it reaches one person
who is not linked to anybody. can anyone tell me how to achieve that
with a control loop?

regards

marc

[monomaniac21]

Re: query db to return all related rows to a given row and all related to those and so on and so on....

btw i forgot to mention more than 1 person can be linked to another
person

[Gary Hasler]

Re: query db to return all related rows to a given row and all relatedto those and so on and so on....

Assuming you're going 2 levels of links, shouldn't it simply require 2
DB queries? Get the Links of JoeBlow, then construct a query to get the
links of all those persons?

monomaniac21 wrote:

btw i forgot to mention more than 1 person can be linked to another
person

[Rik]

Re: query db to return all related rows to a given row and all related to those and so on and so on....

monomaniac21 wrote:

hi all
>
On a networking site i am working on each user has their own id and
the id of another person who they are linked to. what i want to do is
to be able to pull up a list of everyone linked to one person and all
the ppl linked to them. So the query would work something like get id
of linked get linked row get id of linked to this person get of
person get id of linked to person.... this needs to go on until it
reaches one person who is not linked to anybody. can anyone tell me
how to achieve that with a control loop?

First of all, it's not clear to me what you want exactly. You want ALL
persons linked to one, but stop as soon as one of them is not linked to
anyone? This doesn't make sense to me, unless the structure is a clear tree,
with only 'big boss' not linked to anyone for each person in the table. A
bit of insight in your database structure would help.

If you've got a hierarchical tree, then maybe this site will give you some
insight:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

If it's not that simple, a self_reference in PHP would almost certainly be
required.
Pseudo code (all I can do without a proper database explanation):

function get_persons($id ){
global $database, $linked;
if( return false;
$persons = $database->users_linked($ id);
if(!$persons) return false;
foreach($person s as $person){
$linked[] = $person;
if(get_person($ person)===false ) return false;
}
}
$linked = array();
get_persons($id );

Grtz,
--
Rik Wasmus

[Jerry Stuckle]

Re: query db to return all related rows to a given row and all relatedto those and so on and so on....

monomaniac21 wrote:

hi all
>
On a networking site i am working on each user has their own id and the
id of another person who they are linked to. what i want to do is to be
able to pull up a list of everyone linked to one person and all the ppl
linked to them. So the query would work something like get id of linked
get linked row get id of linked to this person get of person get id of
linked to person.... this needs to go on until it reaches one person
who is not linked to anybody. can anyone tell me how to achieve that
with a control loop?
>
regards
>
marc
>

I don't understand. If you're looping through links to other people,
you're *never* going to find anyone who's not linked.

You didn't say which database you're using - but I'd recommend you try a
newsgroup related to your database. You could, for instance, get
everyone linked to a particular person through X levels with one query
in DB2 - but it would be a heck of a query. You probably can do it in
Oracle and SQL Server, also. Don't know about others.

--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===

[Jerry Stuckle]

Re: query db to return all related rows to a given row and all relatedto those and so on and so on....

Rik wrote:

monomaniac21 wrote:
>

>>hi all
>>
>>On a networking site i am working on each user has their own id and
>>the id of another person who they are linked to. what i want to do is
>>to be able to pull up a list of everyone linked to one person and all
>>the ppl linked to them. So the query would work something like get id
>>of linked get linked row get id of linked to this person get of
>>person get id of linked to person.... this needs to go on until it
>>reaches one person who is not linked to anybody. can anyone tell me
>>how to achieve that with a control loop?

>
>
First of all, it's not clear to me what you want exactly. You want ALL
persons linked to one, but stop as soon as one of them is not linked to
anyone? This doesn't make sense to me, unless the structure is a clear tree,
with only 'big boss' not linked to anyone for each person in the table. A
bit of insight in your database structure would help.
>
If you've got a hierarchical tree, then maybe this site will give you some
insight:

http://dev.mysql.com/tech-resources/articles/hierarchical-data.html

>
If it's not that simple, a self_reference in PHP would almost certainly be
required.
Pseudo code (all I can do without a proper database explanation):
>
function get_persons($id ){
global $database, $linked;
if( return false;
$persons = $database->users_linked($ id);
if(!$persons) return false;
foreach($person s as $person){
$linked[] = $person;
if(get_person($ person)===false ) return false;
}
}
$linked = array();
get_persons($id );
>
Grtz,

Rik.

I've seen this type of linking before. It's not really hierarchical -
its a linkage of peers. For instance, A could be linked to B and C, B
to D and E, and E back to A and B.

A lot depends on the database he's using. The more advanced ones can
handle this type of query all in SQL. But it's a heck of a query :-).



--
=============== ===
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
jstucklex@attgl obal.net
=============== ===