Extrapolating Grandparents from Navigation Script

[Chavista]

I have a table that lists animal taxons (species, families, etc.) in a parent-child relationship. The name of each taxon, or scientific name, is in a field named Taxon, and each taxon's parent is in a field named Parent. Consider the following data:

TAXON | PARENT
Animalia | Life
Chordata | Animalia
Mammalia | Chordata
Carnivora | Mammalia
Felidae | Carnivora
Panthera | Felidae
Panthera_leo | Panthera

If I'm on a page with the URL MySite/Life/Panthera_leo, I can easily display the parent (Panthera). But how can I display the grandparent (Felidae), great grandparent (Carnivora), etc.?

I figured out a relatively simply way of doing it. I simply replace the URL with the Parent in a script to get the grandparent, then plug the grandparent's parent into a similar script to get its parent and so on. It looks like this:

$GP = mysql_fetch_assoc(mysql_query("SELECT Taxon, Parent, Rank
 FROM gz_life L
 WHERE Taxon LIKE '$Parent'"));

$Family = $GP['Parent'];

$GGP = mysql_fetch_assoc(mysql_query("SELECT Taxon, Parent, Rank
 FROM gz_life L
 WHERE Taxon LIKE '$Family'"));

$Order = $GGP['Parent'];

$GGGP = mysql_fetch_assoc(mysql_query("SELECT Taxon, Parent, Rank
 FROM gz_life L
 WHERE Taxon LIKE '$Order'"));

$Class = $GGGP['Parent'];

$GGGGP = mysql_fetch_assoc(mysql_query("SELECT Taxon, Parent, Rank
 FROM gz_life L
 WHERE Taxon LIKE '$Class'"));

$Phylum = $GGGGP['Parent'];

$Top = mysql_fetch_assoc(mysql_query("SELECT Taxon, Parent, Rank
 FROM gz_life L
 WHERE Taxon LIKE '$Phylum'"));

$Kingdom = $Top['Parent'];

The only problem is I have to write five separate queries to get from the species level to the animal kingdom. Actually, it doesn't seem to be a problem; it works pretty fast. However, I wondered if might be able to pull the same values out of my navigation script? It displays "bread crumb" links that look something like this:

Animalia > Chordata > Mammalia > Carnivora > Felidae > Panthera > Panthera leo

This is the code:

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

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

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

$mypath = get_path($MyURL, gz_life, Taxon);
$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;";

If I was on a species page, like MySite/Life/Panthera_leo, does anyone know how I could extrapolate the lion's great great grandparent (order Carnivora) from the script above? In other words, I'd like to be able to echo $Order where it displays "Carnivora," while $Class displays "Mammalia," etc.

Thanks for any tips.

[Cups]

I'm not sure how concrete your table structure is, but if you are quite early into it then this old SP article might be helpful. http://www.sitepoint.com/hierarchical-data-database-2/

EDIT

Actually I found the link(s) here, which gives insights into more points of view besides.

[Chavista]

Wow, I remember reading that article years ago. At the time, it was way over my head.

Actually, it still looks a little confusing, but I think maybe I get it now. Am I correct in saying that's simply a different way of querying my table? In other words, I wouldn't have to create an entirely new database table with a different design, would I?

I'll see if I can write a query that will work with my existing table. If I understand correctly, this type of query is faster and gives you more control over the results, right?

Thanks for the tip.

Oops - I just noticed that this query requires to extra fields - left and right. I'll check out the other link you posted. Thanks.

[Chavista]

Wow, so many solutions. They all look interesting, but a little confusing.

One BIG problem with the SitePoint article's ("Storing Hierarchical Database in a Database") is that I'm going to be constantly adding, deleting and sometimes rearranging rows. I currently have about 70,000 rows for vertebrates alone, so reformatting the data each time I change a row could be time consuming. Also, if I'd have to rewrite all my queries if the lion's "left" value changes from 1,013 to 1,014, for example.

I'm still reading the SitePoint article, but I have haven't yet figured out how they created the "right" column. It looks like I could the left field is just a numerical key - I could simply number all the rows in my table 1-70,000. But how do you decide what values go on the right?

[Cups]

Wow, I remember reading that article years ago. At the time, it was way over my head.

Ditto! I am not absolutely sure this is the correct answer to you question -- in any case.

But how do you decide what values go on the right?

If you go back to the article and imagine you were going to add a node, under Food / Fruit / Red and after Cherry and you called it [Strawberry] then that part of the tree would have to have the numbers :

3 [Red] 8
    |
4 [Cherry] 7
    | 
5 [Strawberry] 6

He deals with the complexity of inserting nodes in part three, but I am not sure you are going to be adding many new species are you?

I thought it was telling that one of the responses in the link I provided suggests you just bite the bullet and create loops of queries, but I am not sure how many layers deep you could end up going. Certainly you need to consider that your "breadcrumb" is mirrored in your url paths (writing that reminds me of another similar SP discussion a few years back -- not able to find it, although IIRC it similarly dealt with mammals).

[Chavista]

Thanks for all the tips. I'm definitely going to keep all this info for my future notes.

Unfortunately, yes, I'll have to upgrade my database repeatedly. New species are constantly being discovered, and classification arrangement are constantly changing.

[Cups]

Wow, who knew?

Well the only problem is finding where to insert your node, and then adding +2 to all the following nodes to move them on down and make space for your new one.

Difficult to write but easier to read, it would seem anyway ....