Diaplaying Database Grandparents

[geosite]

I have a table that displays a list of animal taxonomic groups, noting parent-child relationships. For example, the code below illustrates the wolf (Canis lupus) and its parent (genus Canis), grandparent (family Canidae) and so on up to the class Mammalia.

NAME | PARENT | FAMILY | ORDER | CLASS | RANK
Mammalia | Animalia | (NULL) | (NULL) | (NULL) | 25
Carnivora | Mammalia | (NULL) | (NULL) | Mammalia | 35
Canidae | Carnivora | (NULL) | Carnivora | Mammalia | 45
Canis | Canidae | Canidae | Carnivora | Mammalia | 55
Canis_lupus | Canis | Canidae | Carnivora | Mammalia | 65

Notice that I give every taxonomic group a numerical ranking. For example, all species, whether Canis lupus or the blue whale, are 65, while classes (mammals, birds, etc.) are 25.

At the end of this post is a script I use to display a bread crumbs-style navigation links at the top of my page. For example, if a visitor is viewing the wolf page at MySite/Life/Canis_lupus, they would see this:

Animalia > Mammalia > Carnivora > Canidae > Canis > Canis lupus

Notice that the script only uses the fields Name and Parent.

So I can easily display any taxon's parent, and I have a simpler script that displays each taxon's children. For example, the family Canidae's parent is the order Carnivora, and its children include the genus Canis, to which the wolf belongs.

What I'd like to know is if there's a way to display a taxon's GRANDparent, GREAT GRANDparent, GRANDchildren and so on. Obviously, the grandparents are displayed in my navigation links - but only as part of a family tree. How could I display the wolf's great grandparent (Order Carnivora) ONLY? For example, I'd like to be able to type echo $GGP on a page and display "Carnivora." Similarly, I'd like to be able to type $GC (grandchildren) on the Carnivora page and display a list of genera that are associated with the order Carnivora.

I suspect I could create some sort of script that does that using the fields Family, Order and Class. However, I'd like to delete those fields, if possible, leaving the following:

NAME | PARENT | RANK
Mammalia | Animalia | 25
Carnivora | Mammalia | 35
Canidae | Carnivora | 45
Canis | Canidae | 55
Canis_lupus | Canis | 65

Is there a way to display grandparents and grandchildren using only the fields Name and Parent (and perhaps the numerical ranking)?

Thanks.

function get_path($node, gz_mammals, 'Name') {
$result = mysql_query('SELECT Parent FROM gz_mammals WHERE Name = "'.$node.'";');

   $row = mysql_fetch_array($result);
   $path = array();
   if ($row['Parent']!='') {
       $path[] = $row['Parent'];

$path = array_merge(get_path($row['Parent'], gz_mammals, 'Name'), $path);
   }
   return $path;
}

switch ( $MyPage )
{
 case 'ChildPage':
$TopNav = str_replace('Carnivora', '', $TopNav);
 break;
 default:
 break;
}

$mypath = get_path($URL1, gz_mammals, 'Name');
$MyLink = $mypath;
$MyLink = str_replace('Life', '', $MyLink);
$MyDisplay = $mypath;
for($i=0;$i<count($mypath);$i++){
$TopNav = "<a href=\"".$MyLink[$i]."\"> ".$MyDisplay[$i]."</a>&nbsp;&gt;";
$That = array('<a href="Life">', '"> ', '&gt;', '<a href="');
$This = array('<a href="/Life/">', '">', '&gt; ', '<a href="/Life/');
$TopNav = str_replace($That, $This, $TopNav);

$TopNav = str_replace(''.$MyName.'/', '', $TopNav);
echo $TopNav;
}

switch ( $MyPage )
{
 case 'ChildPage':
 echo '<a href="/Life/'.$Name.'">'.$Name.'</a> &gt; <span class="navhere">'.$URL2.'</span>';
 break;
 default:
 echo '<span class="navhere">'.$MyName.'</span><br />'.$ImgTax.'';
 break;
} 

[Jeanco]

I didn't sort through the code you posted, but based on your question about using the new table structure to find GGP or GC, its entirely possible.

Lets look at the table as Child/Parent pairings instead of Name/Parent. So we have (the rank doesn't matter here):

CHILD | PARENT
Mammalia | Animalia
Carnivora | Mammalia
Canidae | Carnivora
Canis | Canidae
Canis_lupus | Canis

Now lets also look at the "parent/grandparent/great grandparent" as layers.

Say we're on the Canidae page. You could do something like this (untested, but the logic is there):

$current = "Canidae" //I assume you are getting this from the DB somehow

//great grandparent = 3 levels deep
$GGP = getParent(getParent(getParent($current)));

//grandchild = 2 levels deep
$GC = getChild(getChild($current));

//Get the Parent
function getParent($current) {
   $query = "SELECT * FROM table WHERE CHILD = '".$current."'";
   $results = mysql_query($query) or die ("An error occurred: ".mysql_error());
   $myrow = mysql_fetch_array($results);
   return $myrow['parent'];
}

//Get the Child
function getChild($current) {
   $query = "SELECT * FROM table WHERE PARENT = '".$current."'";
   $results = mysql_query($query) or die ("An error occurred: ".mysql_error());
   $myrow = mysql_fetch_array($results);
   return $myrow['child'];
} 

You can go as few or as many levels deep as necessary this way. Obviously, some error checking to make sure that the parent or child exists is necessary but this should be a start.

[geosite]

I wasn't aware of the getParent and getChild functions. Thanks for the tips.

[Jeanco]

They're not default functions. I created them (they're in the code).

They are designed to query your database and return the parent or child of whatever it is sent.

[byron3@earthlink]

Create you a family tree object. The object contains the family tree, store the tree in an multidimensional array. The object can parse the tree and return all aspects of the family ( i.e. the parents are one element before, the grandparents are 2 and so on). You can parse the whole tree and by simply calling an attribute ( $tree->Grand_Parent ) have any attribute you want.

[geosite]

Please ignore this post; I solved this particular question.

[geosite]

Create you a family tree object. The object contains the family tree, store the tree in an multidimensional array. The object can parse the tree and return all aspects of the family ( i.e. the parents are one element before, the grandparents are 2 and so on). You can parse the whole tree and by simply calling an attribute ( $tree->Grand_Parent ) have any attribute you want.

How do you create a "family tree object"?