Counting Children in Database Query

PHP
1

I have a database that lists animal taxons - class, order, family, genus, species, etc. - in a parent-child relationship, like this…

(Field Taxon) (Field Parent)
Felidae Carnivora
Panthera Felidae
Panthera_leo Panthera

I use the following query to display a list of each taxon’s children, where $MyURL (e.g. www.mysite.com/Felidae) equals a value in the field Taxon.


$Children = mysql_query ("SELECT L.N, L.Taxon, L.Parent, L.Rank, G.Name_Common
 FROM gz_life AS L
 LEFT JOIN gz_names_genus AS G on G.Taxon = L.Taxon
 WHERE L.Parent = '$MyURL' Order By L.N, L.Rank");

Basically, it simply converts the taxon being displayed to a PARENT, displaying its children by default.

Anyway, how can I display the NUMBER of children? For example, if there are six genera in the family Felidae, I might want to display something like Felidae (6).

Also, is there a way to display grand-children, or a complete sequence of children? I have navigation links that reach from the current taxon back to the animal kingdom, like this…

Animalia > Vertebrata > Mammalia > Carnivora > Felidae

It would be cool if I could type in Carnivora, and it would display the number of children (families) plus the number of grand-children (genera) and the number of great grand-children (species), like this…

Carnivora (26 families, 112 genera, 352 species)

Is there a way to do that?

Thanks.

2

Are you using either of the two methods mentioned in this article to store the relationship between each item?

3

(psst. Based on the L_Parent field, i’m gonna guess it’s Adjacency)

Personally, i’m of the belief that if you’re going to be pulling/analyzing data in this fashion, NSM (the other of the models suggested in said article, with a different title {MPTT}.) is better.

Trying to pull data like ‘how many leaves does this node eventually have’ is rather difficult in Adjacency, -if- the number of generations is variable or unknown. If it’s fixed it becomes simpler.

4

The one-level counting is easy, by the way… [FPHP]mysql_num_rows[/FPHP] the query result.

5

DUH, I wasn’t thinking. Your tips jogged my memory; I simply modified the query I’m using to display pages…


$sql = "SELECT COUNT(Parent) AS numberofurls
  FROM gz_life
  WHERE Parent = '$MyURL'";

$sql_result = mysql_query($sql,$conn);
$result = mysql_fetch_assoc($sql_result);

$sql_result = mysql_query($sql,$conn);
$result = mysql_fetch_assoc($sql_result);

$Number_Children = $result['numberofurls'];

I have another question, though: Is it possible to do a table join with this type of query? I have a separate table that lists animals that are extinct, and I may not want them counted as children. So I’d join that second table, then somehow instruct the query to NOT count children that have a value like ‘Extinct’, ‘Prehistoric’, etc.

Regarding the system I’m using, my main table - gz_life - lists data like this:

(Taxon) (Parent)
Mammalia Vertebrata
Carnivora Mammalia
Felidae Carnivora
Panthera Felidae
Panthera_leo Panthera

I remember reading the article you linked to, or something similar, long ago, and one of the strategies blew me away; it just looked too complex. I think it was the one you call the adjacency strategy. The way I’m doing it - with a database table listing parents and children - seems to work fast enough.

6

the strategy that blew you away was probably the nested set model

the adjacency model is what you currently have (with the parent column)