SitePoint Sponsor

User Tag List

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

    Counting Siblings (Database Query)

    I have a database table that lists animals in a parent/child relationship. I can display a list of all the species (children) in the cat family (parent), for example. I also figured out how to display a taxon's number of children.

    Now I'd like to figure out how to display the number of siblings a taxon has. For example, if there are eight families in the order Carnivora, then the cat family (Felidae) would displayl "7," because it has seven siblings. But if there's just one species in a particular family, then it would display "0" (or no value at all), because it has no siblings.

    It seems like it should be simple to do, but I haven't figured it out yet. One complication is that there's an extra taxonomic rank between family and species (genus) that I'm not currently doing much with. An order is a family's parent, and a family is an order's child. But a species is technically a family's GRANDCHILD, and a family is that species' grandparent.

    Anyway, thanks for any tips.

  2. #2
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    If they're in the same table, you can do a subquery to return the count

    SELECT this,that, (SELECT COUNT(*) FROM tbl WHERE parent_id = t.animal_id) as children_count FROM tbl t

    Also this belongs in the MySQL forum, not here.

  3. #3
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    253
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, that works great!

    I have a follow up question, but I don't know if I should post it here or start a new thread in the MySQL section. This actually seems like more of a PHP problem to me. Anyway, the moderators can move this thread to the MySQL section if they want.

    I'm not sure if Implemented your script perfectly. I simply found a similar script on my site and tweaked it to make it work like your script. This is what I wound up with:

    Code:
    $sql = "SELECT COUNT(*) 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);
    
    switch ($result['numberofurls'])
    {
     case 1:
     echo 'No Siblings';
     break;
    
     case 0:
     echo 'NADA';
     break;  
    
     default:
     echo 'Siblings';
     break;
    }
    Now I'd like to figure out how to make this skip a level. For example, imagine the following values:

    (order) Artiodactyla
    (family) Antilocapridae

    Bovidae is Artiodactyla's child, and it has several siblings, so the script above would display "Siblings"

    Now imagine the following relationship:

    (family) Antilocapridae
    (genus) Antilocapra
    (species) (Antilocapra_americana)

    This time, I want to know how many species are in a family, which means I want to IGNORE genus. In fact, Antilocapra_americana has no siblings; it's the only species in family Antilocapridae.

    So does anyone know if there's a way to write a script that will essentially display the number of siblings for GRANDCHILDREN?

    Thanks.


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
  •