468,272 Members | 2,070 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,272 developers. It's quick & easy.

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

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

Aug 14 '06 #1
5 1078
btw i forgot to mention more than 1 person can be linked to another
person

Aug 14 '06 #2
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
Aug 14 '06 #3
Rik
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/...ical-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($persons as $person){
$linked[] = $person;
if(get_person($person)===false) return false;
}
}
$linked = array();
get_persons($id);

Grtz,
--
Rik Wasmus
Aug 14 '06 #4
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.
js*******@attglobal.net
==================
Aug 15 '06 #5
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/...ical-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($persons 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.
js*******@attglobal.net
==================
Aug 15 '06 #6

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

13 posts views Thread by aaron | last post: by
6 posts views Thread by Xenophobe | last post: by
4 posts views Thread by Orion | last post: by
2 posts views Thread by Fendi Baba | last post: by
2 posts views Thread by cryon.b | last post: by
5 posts views Thread by DeanL | last post: by
reply views Thread by NPC403 | last post: by
reply views Thread by zattat | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.