Re: [PHP-DB] Recursively determine parent records?
http://www.sitepoint.com/print/hierarchical-data-database
has a guide on how to do the same thing but in a single SQL query,
without looping at all. It is very interesting reading and requires a
slight restructuring of how the records are stored to pull off but with
the wonderful result in being much faster to retrieve.
Chris Gregors wrote:
Here's a piece of some code I wrote that does what you want. You'll need
to change the WhoIsMyParent() function to match your database
abstraction and schema.
$array = BreadCrumbs($id);
print_r($array);
exit;
function BreadCrumbs($parent) {
// construct an array of indexes that represents the path back
to the
// root from my current location in the tree.
$PathStack = array();
array_push($PathStack,$parent);
$MyParent = WhoIsMyParent($parent);
while ($MyParent != 0) {// 0 = top of
the tree
$PathStack[] = $MyParent;
$MyParent = WhoIsMyParent($MyParent);
}
$PathStack = array_reverse($PathStack);
return $PathStack;
}
function WhoIsMyParent($id) {
$row=FetchRow(QueryDb(select parent from sometable where
id=.$id));
if ($row == NULL) return 0;
return $row['parent'];
}
-Original Message-
From: Murray @ PlanetThoughtful [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 05, 2004 11:58 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Recursively determine parent records?
Hi All,
I have recordset that contains a hierarchical representation of records
where a record's [parentid] field contains the recid of the record
'above'
the current record.
A representation might look like this:
Recid, parentid, title
1, 0, Top level record
2, 0, Another top level record
3, 1, A record under the top level record
4, 3, Another level
5, 2, A record under the second top level record
If I have currently retrieved the record with recid of 4, I want to work
out
the 'chain' of records that lead back to the top level record it has
been
created under.
In this instance, that chain would look like:
4, 3, Another level
3, 1, A record under the top level record
1, 0, Top level record
I'm wondering if anyone can help me work out how to achieve this?
Many thanks in advance!
Much warmth,
Murray
http://www.planetthoughtful.org/ http://www.planetthoughtful.org
Building a thoughtful planet,
One quirky comment at a time.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Recursively determine parent records?
not to nit-pick:
1. I only do 1 sql query
2. Gijs Van Tulder uses recursion, while I use while loops. Similar
effect.
3. Gijs Van Tulder function only prints out the path as a series of
strings. I give it back in an array.
-Original Message-
From: point of no return [mailto:[EMAIL PROTECTED]
Sent: Thursday, October 07, 2004 2:38 AM
To: [EMAIL PROTECTED]
Subject: Re: [PHP-DB] Recursively determine parent records?
http://www.sitepoint.com/print/hierarchical-data-database
has a guide on how to do the same thing but in a single SQL query,
without looping at all. It is very interesting reading and requires a
slight restructuring of how the records are stored to pull off but with
the wonderful result in being much faster to retrieve.
Chris Gregors wrote:
Here's a piece of some code I wrote that does what you want. You'll
need
to change the WhoIsMyParent() function to match your database
abstraction and schema.
$array = BreadCrumbs($id);
print_r($array);
exit;
function BreadCrumbs($parent) {
// construct an array of indexes that represents the path back
to the
// root from my current location in the tree.
$PathStack = array();
array_push($PathStack,$parent);
$MyParent = WhoIsMyParent($parent);
while ($MyParent != 0) {// 0 = top of
the tree
$PathStack[] = $MyParent;
$MyParent = WhoIsMyParent($MyParent);
}
$PathStack = array_reverse($PathStack);
return $PathStack;
}
function WhoIsMyParent($id) {
$row=FetchRow(QueryDb(select parent from sometable where
id=.$id));
if ($row == NULL) return 0;
return $row['parent'];
}
-Original Message-
From: Murray @ PlanetThoughtful [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 05, 2004 11:58 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Recursively determine parent records?
Hi All,
I have recordset that contains a hierarchical representation of
records
where a record's [parentid] field contains the recid of the record
'above'
the current record.
A representation might look like this:
Recid, parentid, title
1, 0, Top level record
2, 0, Another top level record
3, 1, A record under the top level record
4, 3, Another level
5, 2, A record under the second top level record
If I have currently retrieved the record with recid of 4, I want to
work
out
the 'chain' of records that lead back to the top level record it has
been
created under.
In this instance, that chain would look like:
4, 3, Another level
3, 1, A record under the top level record
1, 0, Top level record
I'm wondering if anyone can help me work out how to achieve this?
Many thanks in advance!
Much warmth,
Murray
http://www.planetthoughtful.org/ http://www.planetthoughtful.org
Building a thoughtful planet,
One quirky comment at a time.
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
smime.p7s
Description: S/MIME cryptographic signature
Re: [PHP-DB] Recursively determine parent records?
Chris Gregors wrote: not to nit-pick: 1. I only do 1 sql query 2. Gijs Van Tulder uses recursion, while I use while loops. Similar effect. 3. Gijs Van Tulder function only prints out the path as a series of strings. I give it back in an array. Actually, if you have more than one parent, you do more than one SQL query since I see WhoIsMyParent() in your code would have to be called multiple times to find multiple levels of parents. The code in the article only does a single query and only does it once (in the 2nd half of the article labelled Modified Preorder Tree Traversal) and can retrieve an entire sub-tree in that one query, so all it has to do is fetch the results without needing to query more to find more than one parent. Which is great if you have a tree that might have parts with hundreds or thousands of levels of parents which still would only be one query away to retrieve. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
RE: [PHP-DB] Recursively determine parent records?
Here's a piece of some code I wrote that does what you want. You'll need
to change the WhoIsMyParent() function to match your database
abstraction and schema.
$array = BreadCrumbs($id);
print_r($array);
exit;
function BreadCrumbs($parent) {
// construct an array of indexes that represents the path back
to the
// root from my current location in the tree.
$PathStack = array();
array_push($PathStack,$parent);
$MyParent = WhoIsMyParent($parent);
while ($MyParent != 0) {// 0 = top of
the tree
$PathStack[] = $MyParent;
$MyParent = WhoIsMyParent($MyParent);
}
$PathStack = array_reverse($PathStack);
return $PathStack;
}
function WhoIsMyParent($id) {
$row=FetchRow(QueryDb(select parent from sometable where
id=.$id));
if ($row == NULL) return 0;
return $row['parent'];
}
-Original Message-
From: Murray @ PlanetThoughtful [mailto:[EMAIL PROTECTED]
Sent: Tuesday, October 05, 2004 11:58 PM
To: [EMAIL PROTECTED]
Subject: [PHP-DB] Recursively determine parent records?
Hi All,
I have recordset that contains a hierarchical representation of records
where a record's [parentid] field contains the recid of the record
'above'
the current record.
A representation might look like this:
Recid, parentid, title
1, 0, Top level record
2, 0, Another top level record
3, 1, A record under the top level record
4, 3, Another level
5, 2, A record under the second top level record
If I have currently retrieved the record with recid of 4, I want to work
out
the 'chain' of records that lead back to the top level record it has
been
created under.
In this instance, that chain would look like:
4, 3, Another level
3, 1, A record under the top level record
1, 0, Top level record
I'm wondering if anyone can help me work out how to achieve this?
Many thanks in advance!
Much warmth,
Murray
http://www.planetthoughtful.org/ http://www.planetthoughtful.org
Building a thoughtful planet,
One quirky comment at a time.
smime.p7s
Description: S/MIME cryptographic signature
