473,229 Members | 1,845 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,229 software developers and data experts.

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 1272
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

13
by: aaron | last post by:
I have a question about (i think) joining. If I have a table in a database that has this info: key - name - favorite 1 - john - 2 2 - judy - 3 3 - joe - 1 the favorite icecream...
6
by: Xenophobe | last post by:
I know this isn't a MySQL forum, but my question is related to a PHP project. I have two tables. table1 table2 "table1" contains 2 columns, ID and FirstName:
29
by: shank | last post by:
1) I'm getting this error: Syntax error (missing operator) in query expression on the below statement. Can I get some advice. 2) I searched ASPFAQ and came up blank. Where can find the "rules"...
4
by: Orion | last post by:
Hi, This is kind of last minute, I have a day and a half left to figure this out. I'm working on a project using ms-sqlserver. We are creating a ticket sales system, as part of the system, I...
0
by: Jeff Boes | last post by:
I hope this helps someone else ... I had struggled some time ago with attempts to get a rank of values query to work, but then I gave up and set it aside. I had another reason to attack it, and in...
2
by: Fendi Baba | last post by:
I created a person table with various fields such as Suffix, Salutation, etc, Some of these fields may not be mandatory for example suffix. In the actual table itself, I only have a field for...
2
by: cryon.b | last post by:
Hi To All, I took up the IBM sample testfor Exam 700 today and I have the test tomorrow,I have some questions for which Iam not sure about the right answer,can anyone please guide me as to what is...
2
by: me | last post by:
I have written a query that joins four or five tables. One table has 30,000 rows. Another table has only 200. I want to only return the 200 or so rows in the smaller table and columns from the...
5
by: DeanL | last post by:
Hi all, I'm trying to set up a query that runs from a command button on a form (simple enough so far), what I want the query to do is take values from the fields on the form (seven fields in...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: Aftab Ahmad | last post by:
Hello Experts! I have written a code in MS Access for a cmd called "WhatsApp Message" to open WhatsApp using that very code but the problem is that it gives a popup message everytime I clicked on...
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.