Suppose I want to create an animal kingdom database that gives me the option of displaying the following taxonomic hierarchies:
1) A bread crumbs navigation string, including ALL taxons; e.g. Animalia (kingdom) > Chordata (phylum) > Vertebrata (a SUBphylum) > Mammalia (class) > Eutheria (a SUBclass) 2) A bread crumbs nav string that excludes taxons beginning with SUB; e.g. Animalia > Chordata > Mammalia 3) Lists of various taxons' children, including ALL taxons; e.g. a. CHORDATA (phylum) b. Vertebrata (subphylum) b. Urochordata (subphylum) 4) Lists of taxons' children, excluding SUBorders, SUBfamilies, etc.; e.g. a. CHORDATA (phylum) b. Mammalia (class) b. Aves (class) b. Reptilia (class), etc. 5. A combination that looks like this: a. CHORDATA (phylum) b. VERTEBRATA c. Mammalia c. Aves c. Reptilia c. Amphibia b. UROCHORDATA And if you clicked on Vertebrata, you'd see only its children, like this: Mammalia Aves Reptilia Amphibia What's the best way to organize a database to achieve this flexibility? My current child-parent relationship generally works, but I think I erred in treating SUBtaxons a little differently, assigning them parents but not children. For example, the data below illustrates how I assigned the phylum Chordata as the parent of both the subphylum Vertebrata and the class Mammalia. Chordata | Animalia Vertebrata | Chordata Mammalia | Chordata So clicking Chordata displays... Vertebrata Mammalia ...and clicking Mammalia displays mammal orders, but clicking Vertebrata displays nothing. Do you think I'll be able to write PHP scripts that do what I want, or does it make more sense to reorganize my database, assigning parents and children to every taxon, then writing PHP scripts that somehow weed out SUBtaxons when I don't want to display them? A third option is to use a recursive array, something I haven't learned how to do yet. If I try a recursive array, I assume I'd have to modify my database so that every taxon does indeed have a parent and a child. If it helps, I posted the code for the two PHP scripts I'm using to make my bread crumbs navigation and to display the children of various taxons. Eventually, I'll have to write a third script that will join tables with additional information on distribution, diet, etc. But I think what I really need now is confirmation that I should assign EVERY taxon a parent and child (or advice to the contrary) and whether I'd be better off sticking with a child-parent relationship or a recursive array. Thanks. [PHP] // http://www.sitepoint.com/article/hierarchical-data-database/2 function display_children($parent, $level) { $result = mysql_query('SELECT Name FROM gzanimals as A WHERE Parent="' . $parent . '";'); while ($row = mysql_fetch_array($result)) { echo str_repeat(' ',$level).$row['Name']."\n"; display_children($row['Name'], $level+1); } } function get_path($node) { $result = mysql_query('SELECT Parent FROM gzanimals '. 'WHERE Name="'.$node.'";'); $row = mysql_fetch_array($result); $path = array(); if ($row['Parent']!='') { $path[] = $row['Parent']; $path = array_merge(get_path($row['Parent']), $path); } return $path; } $mypath = get_path($mycode); for($i=0;$i<count($mypath);$i++){ echo "<a href=\"index.php?taxon=".$mypath[$i]."\"> ".$mypath[$i]." </a> > "; } [/PHP] * * * * * * * * * * This is the script I'm using to display children of various taxons: [PHP] <?php $result = mysql_query('select count(*) from gzanimals'); if (($result) && (mysql_result ($result , 0) > 0)) { } else { die('Invalid query: ' . mysql_error()); } { $taxon = mysql_query ("SELECT Name, Parent FROM gzanimals AS A WHERE Parent = '$_GET[taxon]'"); echo '<div class="' . $_GET['taxon'] . '">'; echo '<table>'; //<!-- BeginDynamicTable --> // while ($row = mysql_fetch_array ($taxon)) { while ($row = mysql_fetch_array($taxon, MYSQL_ASSOC)) { // mysql_fetch_array($taxons, MYSQL_ASSOC) // <td><a href=SitePointA.php?taxon=$taxon>{$row["Name"]}</a></td> echo <<<EOD <tr> <td><a href=index.php?taxon=$row[Name]>{$row["Name"]}</a></td> </tr> EOD; } } echo '</table></div>'; ?> </td> </tr> </table> [/PHP] __________________________________________________ Do You Yahoo!? Tired of spam? Yahoo! Mail has the best spam protection around http://mail.yahoo.com -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
