Need Advice on Animal Kingdom (Recursive array, tree, etc.)

[geosite]

I'm developing a database focusing on the animal kingdom, and I'm not sure exactly how to organize it. I just finished a "first draft" table for mammals that features four key fields:

Name, Tax, Parent, Parent2

...where "Name" is the name of a kingdom, class, order, family, genus or species, "Tax" defines that name as an order, family, etc. and Parent is that name's Parent. I'll explain Parent 2 shortly. First let me give you an example, focusing on the wolf:

NAME | TAX | PARENT
Mammalia | cla | Chordata
Carnivora | ord | Mammalia
Canidae | fam | Carnivora
Canis | gen | Canidae
lupus | spp | Canis

In the examples above, cla, ord, fam, gen and spp represent class, order, family, genus and species.

So I'm trying to figure out how to organize and display this database so that I can print out a complete taxonomic hierarchy. For example, I know how to display a particuar species - like the wolf - and I can easily display its parent. But how do I display this?:

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

I've read a little about recursive arrays and nodes (even more confusing), but I want to make sure I make the right choice before I proceed further.

I'm thinking of using the Animal Diversity Web as a model. See this page as an example:

http://animaldiversity.ummz.umich.ed...nis_lupus.html

But I'm also thinking of using Wikipedia as a guide. I thinik it would be cool if visitors could type in the name of a class, order, family or species and retrieve an article, with a "bread crumbs" type navigation link at the top. But I'd also like to have static pages for each entry that register in search engines. (Do I have to create each page manually? I've been told that some databases automatically create pages that register with search engines.)

The field Parent2 simply denotes an alternate parent, such as a subphylum, subfamily, etc.

NAME | TAX | PARENT | PARENT2
Mammalia | cla | Chordata | Vertebrata
Carnivora | ord | Mammalia | Eutheria
Canidae | fam | Carnivora | (NULL)
Canis | gen | Canidae | (NULL)
lupus | spp | Canis | (NULL)

Thus, I could begin the wolf link with Animalia > Chordata (the chordates), or I could begin it with Animalia > Chordata > Vertebrata (a subphylum of the chordates).

Or I may do it like this:

Animalia > Chordata (Vertebrata) > Mammalia

I'd also like to experiment with Windows-style collapsible trees.

Sorry if I overloaded you with questions. I just want to give you an idea of what I'm trying to do and solicit suggestions for organizing my database. Is a simple table that lists children and parents OK, or should I create separate tables for each taxonomic group - orders, families, species, etc.? Or is there another method I should try?

Thanks.

[longneck]

i really like this method of organizing hierarchies:
http://www.sitepoint.com/article/hie...ata-database/2

it makes it VERY esay to find all of the parents of any particular node.

[geosite]

i really like this method of organizing hierarchies:
http://www.sitepoint.com/article/hie...ata-database/2

it makes it VERY esay to find all of the parents of any particular node.

Thanks. That looks interesting, but I don't know if it would work for my project. For example, my mammals table has about 6,000 rows, so it would a nightmare "walking around" each genus within each family within each order, etc.

[longneck]

i don't quite understand what you mean by "walking around". the beauty of MPTT is that you only need to specify the parent of each element, then the code automatically orders them. there is nothing manual about it.

[geosite]

On second thought, it looks like the Tree of Life project uses nodes...

http://tolweb.org/tree/home.pages/infodesign.html

...so I guess it can be done - somehow!

[geosite]

i don't quite understand what you mean by "walking around". the beauty of MPTT is that you only need to specify the parent of each element, then the code automatically orders them. there is nothing manual about it.

OK, I think I understand now...rather than number of every single species, I'd number their parents (genera), then number each genus' parent (family), and so on - right?

That still seems a little daunting, though. Their example uses Food, which is subdivided into Fruit and Meat, each of which is further subdivided. If I equate each of these categories with a taxonomic cateogry, it looks something like this:

Food = Mammals
Fruit & Meat = Orders
Each divided into Families
Each Family divided into Genera
Each Genus divided into Species

So assigning numerals to each of the nodes - everything except species - looks like a huge task. The monotremes (egg-laying mammals) might be comparable to the Meat/Fruit example. But there are several orders of marsupials, with many families and genera, and the placental mammals are far more diverse. There are dozens of genera in the rodent order alone.

Or is there an easier way to number the nodes?

Thanks.

[longneck]

nope, you're missing the point. here are some sample records (which is what you would enter:

name,parent,left_id,right_id
animal,NULL,NULL,NULL
chordate,animal,NULL,NULL
mammal,chordate,NULL,NULL
primate,mammal,NULL,NULL
lemuridae,primate,NULL,NULL
lemur,lemuridae,NULL,NULL
ring-tailed lemur,lemur,NULL,NULL

notice all of the nulls. animal has a NULL because it has no parent. all of the others get NULL for left_id and right_id because they haven't been sorted in to the tree yet.

after inserting these records, you run the php code (or mysql stored procedure) to add the numbers. THIS PART IS AUTOMATIC (you don't have to number anything by hand) and the results are:

name,parent,left_id,right_id
animal,NULL,1,14
chordate,animal,2,13
mammal,chordate,3,12
primate,mammal,4,11
lemuridae,primate,5,10
lemur,lemuridae,6,9
ring-tailed lemur,lemur,7,8

now lets say you wanted to add another type of lemur, the longnecked lemur. )) you would only have to add one record:

longnecked lemur,lemur,NULL,NULL

then you re-run the code and it renumbers the entire tree AUTOMATICALLY:

name,parent,left_id,right_id
animal,NULL,1,16
chordate,animal,2,15
mammal,chordate,3,14
primate,mammal,4,13
lemuridae,primate,5,12
lemur,lemuridae,6,11
ring-tailed lemur,lemur,7,10
longnecker lemur,lemur,8,9

the only thing complicating this will be if parents are not unique to a classification type, i.e. the is a genus and species called human. you will need to add another column to identify what kind of record it is. not terribly difficult.

[designationlocut]

I like longneck's method of organisation. Although from what I understand of Taxonomies, there is only ever one child to a parent species if you get my meaning. So I would suggest a modification to lingneck's structure i.e.

*nav_id   name_title   tax_title   parent_id child_id

* Denotes primary ley.

Both parent and child are set with a nav_id value.

Here if the child id is null, then you have reached the species level.

At top level, the parent id is null.

So if the database is queried where the child id is null, you get a list of species, which you can work backwards from. This will produce your taxonavigation tree for a particular species. In addition any level of the tree can be queried which will allow you to select a list of parents and children from that point.

[geosite]

OK, that makes everything much easier to understand. In fact, it looks like I can easily convert my table by just adding a couple columns.

Suppose my table contained only the following data:

CHILD | PARENT
(NULL) | Animalia
Mammalia | Animalia
Carnivora | Mammalia
Canidae | Carnivora
Canis | Canidae
wolf | Canis

To convert it to a node system, I would add two columns and name them anything I want:

CHILD | PARENT | HotNode | ColdNode
(NULL) | Animalia | (NULL) | (NULL)
Mammalia | Animalia | (NULL) | (NULL)
Carnivora | Mammalia | (NULL) | (NULL)
Canidae | Carnivora | (NULL) | (NULL)
Canis | Canidae | (NULL) | (NULL)
wolf | Canis | (NULL) | (NULL)

And I'm now ready for action, right?

To display a certain set of data, I'd then use the following query:

SELECT * FROM tree WHERE HotNode BETWEEN 2 AND 11;

...and if it doesn't display what I want, I'd just change "BETWEEN 2 AND 11" to "BETWEEN 3 and 14" or whatever, until it displays what I want, right?

[longneck]

d brings up a good point. you should refer to them with id's instead of the names as follows as that will eliminate the problem where a genus and a species could be the same:

id,name,parent,left_id,right_id
113,animal,NULL,1,16
114,chordate,113,2,15
115,mammal,114,3,14
116,primate,115,4,13
117,lemuridae,116,5,12
118,lemur,117,6,11
119,ring-tailed lemur,118,7,10
120,longnecker lemur,118,8,9

however, the problem with representing child and parent on each record instead of using MPTT is that you can't pull all of the parents of one node in a single query. MPTT allows you to do that easily.

[geosite]

d brings up a good point. you should refer to them with id's instead of the names as follows as that will eliminate the problem where a genus and a species could be the same:

OK, that's a good tip; that is one of the biggest problems I've been wrestling with.

I'm stuck on the rest, though.

I added two new fields to my database table, named "left" and "right." I designated each column INT,4,NULL, and didn't put any numerals in them; they're empty.

Then I adapted the tutorial's query to my table (gzmammals1) and columns:

$res = mysql_query ("SELECT * FROM gzmammals1 WHERE left BETWEEN 2 AND 11 ORDER BY left ASC;"); 

But all I get is an error message. The basic query's OK, because this does work:

$res = mysql_query ("SELECT * FROM gzmammals1;"); 

Also, I don't understand what you mean by this:

However, the problem with representing child and parent on each record instead of using MPTT is that you can't pull all of the parents of one node in a single query. MPTT allows you to do that easily.

I thought I was doing the same thing he's doing in the tutorial. His Parent/Title fields are the same as my Parent/Child columns, aren't they?

If not, what do I need to change?

Thanks.

[longneck]

no, you applied my comment to the wrong person. just ignore it. i was commenting on designationlocut's alternate hieracrhy suggestion.

you're getting an error because you used left and right. both of those are reserved words. re-read the article and you'll see the original author addresses that and suggests using lt and rt instead of left and right.

[designationlocut]

*nav_id   name_title   tax_title   parent_id child_id

Considering my structure above, assume a visitor has come to your site, you have listed each species. Lets assume the species has the nav_id of 20, you would get the species like this:

$res = mysql_query ("SELECT * FROM tablename WHERE nav_id = 20"); 

This would produce for example:

20  Species  N. grandis  57 null

Then you would use the parent id to point to the nav id in the table recurse the query until the parent id is null, then you will have a complete tree.

[geosite]

no, you applied my comment to the wrong person. just ignore it. i was commenting on designationlocut's alternate hieracrhy suggestion.

you're getting an error because you used left and right. both of those are reserved words. re-read the article and you'll see the original author addresses that and suggests using lt and rt instead of left and right.

OK, I renamed my fields "lt" and "rt," but it still isn't working. I've been playing with different variations, like these:

$res = mysql_query ("SELECT * FROM gzmammals1 WHERE lt BETWEEN 2 AND 11;");

$res = mysql_query ("SELECT * FROM gzmammals1 WHERE lt = 0;");

$res = mysql_query ("SELECT * FROM gzmammals1 WHERE lt > 5;"); 

I don't see any error messages, but nothing is displaying, either. Thanks.

[geosite]

Oops, I just now spotted your post...

*nav_id   name_title   tax_title   parent_id child_id

Considering my structure above, assume a visitor has come to your site, you have listed each species. Lets assume the species has the nav_id of 20, you would get the species like this:

$res = mysql_query ("SELECT * FROM tablename WHERE nav_id = 20"); 

This would produce for example:

20  Species  N. grandis  57 null

Then you would use the parent id to point to the nav id in the table recurse the query until the parent id is null, then you will have a complete tree.

In fact, I already have a numerical key (ID) in place. I named it NMammals. I plugged in your query...

$res = mysql_query ("SELECT * FROM gzmammals1 WHERE NMammals = 20"); 

...and it just happened to land on a genus (Caluromysiops), not a species. (I've listed all the orders, families, genera and species in one big column, numbered 1 to approximately 6,000.)

Now, assuming Caluromysiops' parent is the family Macropodidae, it might look something like this in the table...

NAME | PARENT
18 | Macropodidae (family) | Diprotodontia (order)
19 | Digeroops | Macropodidae
20 | Caluromysiops | Macropodidae

So Caluromysiops' parent ID is 18, and the next step is this:

Then you would use the parent id to point to the nav id in the table recurse the query until the parent id is null, then you will have a complete tree.

How do I do that again? You probably just showed me, but I'm confused.

Thanks.

[EDITED]

Looking at your earlier post, it looks like you assigned a child ID and parent ID in the same row...

*nav_id name_title tax_title parent_id child_id

Until I modify my table, I assume I can use the parent NAME in lieu of the ID, right?

So here's another example:

ID | CHILD | PARENT | TAX
17 | Canis | Canidae | Genus
18 | lupus | Canis | Species

If I use this query...

$res = mysql_query ("SELECT * FROM gzmammals1 WHERE NMammals = 20"); 

...it will display the species name, lupus. If I want to display ALL the species listed under Canis, I would somehow plug in the name of the parent (Canis)?

[longneck]

did you write any php code to nuber the entires? mysql doesn't do that for you.

[geosite]

did you write any php code to nuber the entires? mysql doesn't do that for you.

Yes, I've listed all the mammal orders, species, etc. in one column in one table, adjacent to a numerical key, numbered 1 to approximately 6,000. So here's a simplified sample row:

ID | NAME | TAX | PARENT | LT | RT
20 | Canis | genus | Canidae | (NULL) | (NULL)

[geosite]

Yes, I've listed all the mammal orders, species, etc. in one column in one table, adjacent to a numerical key, numbered 1 to approximately 6,000. So here's a simplified sample row:

ID | NAME | TAX | PARENT | LT | RT
20 | Canis | genus | Canidae | (NULL) | (NULL)

[EDITED]

Oops, you said "PHP" code. I'm confused again.

I don't need to insert any numerals in the LT or RT fields, right? The tutorial suggests that these numerals are created by the database or query. Later it lists the following PHP script:

<?php
function display_tree($root) {
   // retrieve the left and right value of the $root node
   $result = mysql_query('SELECT lft, rgt FROM tree '.
                          'WHERE title="'.$root.'";');
   $row = mysql_fetch_array($result);

   // start with an empty $right stack
   $right = array();

   // now, retrieve all descendants of the $root node
   $result = mysql_query('SELECT title, lft, rgt FROM tree '.
                          'WHERE lft BETWEEN '.$row['lft'].' AND '.
                          $row['rgt'].' ORDER BY lft ASC;');

   // display each row
   while ($row = mysql_fetch_array($result)) {
       // only check stack if there is one
       if (count($right)>0) {
           // check if we should remove a node from the stack
           while ($right[count($right)-1]<$row['rgt']) {
               array_pop($right);
           }
       }

       // display indented node title
       echo str_repeat('  ',count($right)).$row['title']."\n";

       // add this node to the stack
       $right[] = $row['rgt'];
   }
}
?>

But I haven't got that far yet.

[designationlocut]

*nav_id   name_title   tax_title   parent_id child_id

Substitute your own below.

$res = mysql_query ("SELECT * FROM table WHERE nav_id = 20"); 

This query has to be your species, ie child id is null.

Ten you recurse until your parent node is null

while (parent_id <> null) {
  $taxnavstring .= $name_title.tax_title . ">" .$taxnavstring;
  $res = mysql_query ("SELECT * FROM table WHERE nav_id = parent_id");
} 

[geosite]

*nav_id   name_title   tax_title   parent_id child_id

Substitute your own below.

$res = mysql_query ("SELECT * FROM table WHERE nav_id = 20"); 

This query has to be your species, ie child id is null.

Ten you recurse until your parent node is null

while (parent_id <> null) {
  $taxnavstring .= $name_title.tax_title . ">" .$taxnavstring;
  $res = mysql_query ("SELECT * FROM table WHERE nav_id = parent_id");
} 

Sorry, I'm confused again. You say "child is null." There are no NULL cells at all in my "Name" column, which lists around 30 orders, several dozen families and a few thousand species. So I don't understand how a child can be NULL.

[EDIT]

I'm off on an errand, so it may be a few hours before I can reply again. Thanks for all the tips.

[designationlocut]

Sorry, I'm confused again. You say "child is null." There are no NULL cells at all in my "Name" column, which lists around 30 orders, several dozen families and a few thousand species. So I don't understand how a child can be NULL.

When you get down to the species level of a taxonavigation tree, does the tree continue with anything? Its the child_id that is null not the name.

[longneck]

But I haven't got that far yet.

well, that's why none of your queries return anything. that php code is what numbers your rows so you can retrieve them using those queries.

[geosite]

When you get down to the species level of a taxonavigation tree, does the tree continue with anything? Its the child_id that is null not the name.

No, there are no units below the "smallest" children - species. For example, the row for the wolf might look something like this:

ID | NAME (Child) | TAXON | PARENT
50 | lupus | spp | Canis

Canis lupus (the wolf) can't have any children, unless I break it down into subspecies.

[geosite]

well, that's why none of your queries return anything. that php code is what numbers your rows so you can retrieve them using those queries.

OK, I copied that script and adapted it to my table. I assume I can simply delete my original query:

$res = mysql_query ("SELECT * FROM gzmammals1 WHERE NMammals = 20"); 

...and replace it with the script, which now looks like this:

function display_gzmammals1($root) {
   // retrieve the left and right value of the $root node
   $result = mysql_query('SELECT lt, rt FROM gzmammals1 '.
                          'WHERE Name="'.$root.'";');
   $row = mysql_fetch_array($result);

   // start with an empty $right stack
   $right = array();

   // now, retrieve all descendants of the $root node
   $result = mysql_query('SELECT Name, lt, rt FROM gzmammals1 '.
                          'WHERE lt BETWEEN '.$row['lt'].' AND '.
                          $row['rt'].' ORDER BY lt ASC;');

   // display each row
   while ($row = mysql_fetch_array($result)) {
       // only check stack if there is one
       if (count($right)>0) {
           // check if we should remove a node from the stack
           while ($right[count($right)-1]<$row['rt']) {
               array_pop($right);
           }
       }

       // display indented node title
       echo str_repeat('  ',count($right)).$row['Name']."\n";

       // add this node to the stack
       $right[] = $row['rt'];
   }
} 

But I get this error message:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\sites\GeoZoo\public_html\practice\tab_ani.php

I replaced "tree" with the name of my table, and I assume I also need to change display_tree to display_gzmammals1. I also change his "title" to "Name," then changed lgt and rgt to lt and rt to match my table.

I suspect the problem may be $root. Am I supposed to replace that with a value? For example, if I want to display everything that belongs to the order Carnivora, do I change $root to $Carnivora, or something (that doesn't work)?

Thanks.

[designationlocut]

function display_gzmammals1($root) {
   // retrieve the left and right value of the $root node
   $result = mysql_query('SELECT lt, rt FROM gzmammals1 '.
                          'WHERE Name="'.$root.'";');
   $row = mysql_fetch_array($result);

   // start with an empty $right stack
   $right = array();

   // now, retrieve all descendants of the $root node
   $result = mysql_query('SELECT Name, lt, rt FROM gzmammals1 '.
                          'WHERE lt BETWEEN '.$row['lt'].' AND '.
                          $row['rt'].' ORDER BY lt ASC;');

   // display each row
   while ($row = mysql_fetch_array($result)) {
       // only check stack if there is one
       if (count($right)>0) {
           // check if we should remove a node from the stack
           while ($right[count($right)-1]<$row['rt']) {
               array_pop($right);
           }
       }

       // display indented node title
       echo str_repeat('  ',count($right)).$row['Name']."\n";

       // add this node to the stack
       $right[] = $row['rt'];
   }
} 

But I get this error message:

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in C:\sites\GeoZoo\public_html\practice\tab_ani.php


I replaced "tree" with the name of my table, and I assume I also need to change display_tree to display_gzmammals1. I also change his "title" to "Name," then changed lgt and rgt to lt and rt to match my table.

I suspect the problem may be $root. Am I supposed to replace that with a value? For example, if I want to display everything that belongs to the order Carnivora, do I change $root to $Carnivora, or something (that doesn't work)?

Thanks.

No I think it might be your database connection, you need to define it within the function.

If a species doesn't have any children, then child_id is null as you are at the bottom of the tree. You start there, and them work your way backwards until the parent_id is null (at the top of the tree).