SitePoint Sponsor

User Tag List

Results 1 to 6 of 6
  1. #1
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Counting Children in Database Query

    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.

    Code:
    $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. #2
    From space with love silver trophy
    SpacePhoenix's Avatar
    Join Date
    May 2007
    Location
    Poole, UK
    Posts
    5,072
    Mentioned
    103 Post(s)
    Tagged
    0 Thread(s)
    Are you using either of the two methods mentioned in this article to store the relationship between each item?
    Community Team Advisor
    Forum Guidelines: Posting FAQ Signatures FAQ Self Promotion FAQ
    Help the Mods: What's Fluff? Report Fluff/Spam to a Moderator

  3. #3
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    (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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  4. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    The one-level counting is easy, by the way... mysql_num_rows the query result.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    265
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    DUH, I wasn't thinking. Your tips jogged my memory; I simply modified the query I'm using to display pages...

    Code:
    $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. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,338
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    the strategy that blew you away was probably the nested set model

    the adjacency model is what you currently have (with the parent column)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •