 | | | Child-Parent Relationships with 2 Parents | Child-Parent Relationships with 2 Parents 2005-08-12 - By David Blomstrom
Back
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/mysql?unsub=mysql@(protected)
Earn $52 per hosting referral at Lunarpages.
|
|
|