SitePoint Sponsor

User Tag List

Results 1 to 7 of 7

Hybrid View

  1. #1
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Cool Extrapolating Grandparents from Navigation Script

    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:

    Code:
    $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:

    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.

  2. #2
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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.

  3. #3
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  4. #4
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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?

  5. #5
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    Quote Originally Posted by Chavista View Post
    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.

    Quote Originally Posted by Chavista View Post
    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 :

    Code:
    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).

  6. #6
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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.

  7. #7
    SitePoint Wizard silver trophybronze trophy Cups's Avatar
    Join Date
    Oct 2006
    Location
    France, deep rural.
    Posts
    6,869
    Mentioned
    17 Post(s)
    Tagged
    1 Thread(s)
    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 ....


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •