0

I'm developing a website focusing on plants and animals. I have scripts that display parents, grandparents and children. But I'd like to learn how to display lists of grandchildren, great-grandchildren, etc.

Taxons (field Taxon) are listed in my database in a parent-child relationship. The field Rank includes a numerical rank for each taxon (e.g. 65 for species). The relationship looks something like this:

Chordata (phylum, Rank = 15)
Mammalia (class, Rank = 25)
Carnivora (order, Rank = 35)
Felidae (family, Rank = 45)
Panthera (genus, Rank = 55)
Panthera-leo (species, Rank = 65)

The taxons also match URL's. For example, you would navigate to MySite/life/panthera-leo to find information about the lion (Panthera leo).

Here's a script I'm currently using to display the number of siblings:

$stm = $pdo->prepare("select L.Taxon, count(*) - 1 Siblings
from gz_life_mammals L
inner join gz_life_mammals L on g.Parent = L.Parent
where L.Taxon = :MyURL
group by L.Parent");
$stm->execute(array(
'MyURL'=>$MyURL,
));

while ($row = $stm->fetch())
{
 $SiblingsCount = $row['Siblings'];
}

And here's the query I'm using to display a list of siblings by name:

$stm = $pdo->prepare("SELECT Taxon, NameCommon, Parent from gz_life_mammals
 WHERE Parent = :Parent AND Taxon <> :MyURL
 ORDER BY NameCommon, Taxon");
 $stm->execute(array(
 'MyURL'=>$MyURL,
 'Parent'=>$Parent
 ));

while ($row = $stm->fetch())
{
 $Taxon = $row['Taxon'];
 $NameCommon = $row['NameCommon'];
}

Suppose we navigate to MySite/life/Mammalia (a class). I want to display the number of orders, families, genera and species in that class. Should I write separate queries for each of those, or is there a way to fetch the numbers of children, grand-children, etc. in one query?

To clarify, I guess I'm more interested in displaying the NUMBER of grand-children, etc. It's relatively easy to display a list of children's names, but a list of grand-children would generally take up too much space. However, it would be really cool to let visitors know the number of children, grand-children, great-grandchildren, etc. in a certain taxon. Can anyone suggest a good query for doing that?

ON EDIT: Here's a better idea of the database schema.

Taxon | NameCommon | Parent | Rank

Mammalia | mammal | Chordata | 25

Carnivora | carnivoran | Mammalia | 35

Ursidae | bear | Carnivora | 45

Ursus | familiar bears | Ursidae | 55

Ursus-arctos | brown bear | Ursus | 65

In this particular example, Mammalia would have (I'm guessing) about 15 children (Carnivora and all the other orders of mammals). It would have perhaps 75 grand-children (mammal families), over 500 great grand-children (genera) and roughly 8,000 great-great grand-children (species).

Family Ursidae, on the other hand, would have three or four children (including genus Ursus) and half a dozen grand-children (species).

  • Without an understanding of the data, there is no way I can see to help. There will always be a parent, but not always a child. This solution sounds like there will be a recursive answer, find a member that has no children and work back to build the relationship hierarchy. – Twisty Oct 19 '15 at 05:45
  • use a table with a parentId. A self-join table. And a stored proc. Like [this](http://stackoverflow.com/a/31967073/1816093) that I wrote up – Drew Oct 19 '15 at 05:54
  • Twisty - Sorry about that. I edited my original post, adding some sample rows at the bottom. Drew - Thank, but I think that's a little over my head. I'll take another look at it, with fingers crossed. ;) –  Oct 19 '15 at 20:11

0 Answers0