SitePoint Sponsor |
|
User Tag List
Results 1 to 25 of 28
-
Jul 28, 2005, 02:52 #1
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Need Advice on Animal Kingdom (Recursive array, tree, etc.)
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.
-
Jul 28, 2005, 06:38 #2
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 28, 2005, 08:56 #3
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by longneck
-
Jul 28, 2005, 09:02 #4
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 28, 2005, 09:12 #5
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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!
-
Jul 28, 2005, 09:53 #6
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by longneck
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.
-
Jul 28, 2005, 10:23 #7
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
nope, you're missing the point. here are some sample records (which is what you would enter:
Code: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
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:
Code: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
)) you would only have to add one record:
Code:longnecked lemur,lemur,NULL,NULL
Code: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
-
Jul 28, 2005, 10:48 #8
- Join Date
- May 2005
- Posts
- 11
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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.
Code:*nav_id name_title tax_title parent_id child_id
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.
-
Jul 28, 2005, 10:57 #9
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
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?
-
Jul 28, 2005, 11:08 #10
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
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:
Code: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
-
Jul 28, 2005, 11:23 #11
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by longneck
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:
PHP Code:$res = mysql_query ("SELECT * FROM gzmammals1 WHERE left BETWEEN 2 AND 11 ORDER BY left ASC;");
PHP Code:$res = mysql_query ("SELECT * FROM gzmammals1;");
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.
If not, what do I need to change?
Thanks.
-
Jul 28, 2005, 11:48 #12
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
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.
-
Jul 28, 2005, 11:54 #13
- Join Date
- May 2005
- Posts
- 11
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Code:*nav_id name_title tax_title parent_id child_id
PHP Code:$res = mysql_query ("SELECT * FROM tablename WHERE nav_id = 20");
Code:20 Species N. grandis 57 null
-
Jul 28, 2005, 12:00 #14
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by longneck
PHP Code:$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;");
-
Jul 28, 2005, 12:10 #15
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Oops, I just now spotted your post...
Originally Posted by designationlocut
PHP Code:$res = mysql_query ("SELECT * FROM gzmammals1 WHERE NMammals = 20");
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.
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
So here's another example:
ID | CHILD | PARENT | TAX
17 | Canis | Canidae | Genus
18 | lupus | Canis | Species
If I use this query...
PHP Code:$res = mysql_query ("SELECT * FROM gzmammals1 WHERE NMammals = 20");
-
Jul 28, 2005, 12:13 #16
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
did you write any php code to nuber the entires? mysql doesn't do that for you.
-
Jul 28, 2005, 12:17 #17
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by longneck
ID | NAME | TAX | PARENT | LT | RT
20 | Canis | genus | Canidae | (NULL) | (NULL)
-
Jul 28, 2005, 12:21 #18
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by geosite
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 Code:<?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'];
}
}
?>
-
Jul 28, 2005, 12:27 #19
- Join Date
- May 2005
- Posts
- 11
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Code:*nav_id name_title tax_title parent_id child_id
PHP Code:$res = mysql_query ("SELECT * FROM table WHERE nav_id = 20");
Ten you recurse until your parent node is null
PHP Code:while (parent_id <> null) {
$taxnavstring .= $name_title.tax_title . ">" .$taxnavstring;
$res = mysql_query ("SELECT * FROM table WHERE nav_id = parent_id");
}
-
Jul 28, 2005, 12:34 #20
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by 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.
[EDIT]
I'm off on an errand, so it may be a few hours before I can reply again. Thanks for all the tips.
-
Jul 28, 2005, 12:37 #21
- Join Date
- May 2005
- Posts
- 11
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by geosite
-
Jul 28, 2005, 12:41 #22
- Join Date
- Feb 2004
- Location
- Tampa, FL (US)
- Posts
- 9,854
- Mentioned
- 1 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by geosite
-
Jul 28, 2005, 15:14 #23
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by designationlocut
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.
-
Jul 28, 2005, 15:21 #24
- Join Date
- Jan 2004
- Location
- Seattle
- Posts
- 4,328
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by longneck
PHP Code:$res = mysql_query ("SELECT * FROM gzmammals1 WHERE NMammals = 20");
PHP Code: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'];
}
}
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.
-
Jul 28, 2005, 17:00 #25
- Join Date
- May 2005
- Posts
- 11
- Mentioned
- 0 Post(s)
- Tagged
- 0 Thread(s)
Originally Posted by geosite
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).
Bookmarks