Displaying number of grand-children using rank column

Question

I'm developing a website focusing on animals and have been struggling to figure out how to display the number of a taxon's grand-children, great grand-children, etc.

This script appears to do a good job displaying the number of children, except that it displays senseless numbers like 13 for species, which should have 0 children.

$stm = $pdo->prepare("SELECT Ch2.Taxon, COUNT(*) - 0 Children
 FROM gz_life_mammals Ch
 LEFT OUTER JOIN gz_life_mammals Ch2 on Ch.Taxon = Ch2.Taxon
 WHERE Ch2.Parent = :MyURL
 GROUP BY Ch.Parent");
 $stm->execute(array(
 'MyURL'=>$MyURL,
));

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

echo $ChildrenCount;

I haven't been able to make it display the correct number of grand-children; the results are wildly irregular.

So I came up with another scheme. Below is some data from my database table, arranged in a parent-child relationship.

**Taxon | Parent | Rank**

Mammalia | Chordata | 25

Carnivora | Mammalia | 35

Canidae | Carnivora | 45

Canis | Canidae | 55

Canis-lupus | canis | 65

Suppose I navigated to the page MySite/life/carnivora, and I want to know how many species are in that order. That means I have to determine how many great grand-children Carnivora has.

If I can't do it the traditional way, then maybe I can make it work backwards. I simply write a query that counts every row where the value for the field Rank is '65' and whose great grandparent is 'Carnivora.'

Does anyone now how to write a query like that?

score 0 · Accepted Answer · edited May 23 '17 at 11:46

I thought your problem could benefit from a recursive query. Not possible in MySql apparently so I looked for a workaround. So: taken basically character for character from this Q/A. Linked to there from this. Found that through big oogling search engine.

I'm hesitant to post this answer because i'm not entirely sure what's going on here. (for instance, I don't know if the "danger" referred to in the 1st linked post would matter in your case. You'll definitely want to be careful before you use this on a live system < /CMA>

This query returns the number of descendants :MyUrl has.

SELECT  COUNT(@id := (
        SELECT  `taxon`
        FROM    gz_life_mammals
        WHERE   `parent` = @id
    )) AS numDescendants
FROM (
    SELECT  @id := :MyUrl
) vars
STRAIGHT_JOIN gz_life_mammals
WHERE @id IS NOT NULL;

Of interest, if you remove the COUNT() from around the @id := (SELECT ... than you get a list of all descendants plus one NULL row.

Intriguing. I tried it, and it correctly displays a count of 0 on the species level. When I display genus page (a species' parent), I get an error message - Cardinality violation: 1242 Subquery returns more than 1 row' That suggests (to me) that it is searching for the information. — , Oct 20 '15 at 22:57
@DavidBlomstrom dunno what to tell you. I've taken your sample data, created a table, run the query in PHP using PDO just like you do except with my query and i get a 3 if I set $MyUrl to `'Carnivora'`. Out of curiosity, what version of MySQL are you running? — , Oct 21 '15 at 00:46

Displaying number of grand-children using rank column

1 Answers1