SitePoint Sponsor

User Tag List

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

    Can you improve my sloppy grouping script?

    I've been working on a feature that places plants and animals in various groups (e.g. marine mammals, white mammals, animals with horns or antlers, etc.). The key to the project is the table gz_groups2, especially the columns Taxon and Group. Taxon simply identifies various species, families, etc., while Group identifies the groups they belong to. For example, the data below shows that all members of the order Cetacea (whales) are marine mammals, as is the polar bear (Ursus maritimus). The polar bear also belongs to a second group, white mammals.

    Taxon | Group
    Cetacea | Marine Mammals
    Ursus_maritimus | Marine Mammals
    Ursus_maritimus | White Mammals

    Here's the problem: If my browser is pointed at the URL MySite/Life/Cetacea or MySite/Life/Ursus_maritimus, then everything clicks. But what if I navigate to a page representing an individual whale species, like the killer whale - MySite/Life/Orcinus_orca ? How is my script supposed to know that the killer whale belongs to the order Cetacea and is therefore a marine mammal?

    I solved the problem with the query below...

    Code:
    $Groups = $pdo->prepare("SELECT G2.N, G2.Taxon, G2.Group, G1.N, G1.Group, G1.Category, G1.URL, G1.Link
     FROM gz_groups2 AS G2
     LEFT JOIN gz_groups1 G1 ON G1.Group = G2.Group
     WHERE Taxon = :Kingdom
     OR Taxon = :Phylum
     OR Taxon = :Class
     OR Taxon = :Order
     OR Taxon = :Family
     OR Taxon = :Genus
     OR Taxon = :MyURL
     GROUP BY G1.Group
     Order By G2.N");
    $Groups->execute(array(
     'Kingdom'=>$Kingdom,
     'Phylum'=>$Phylum,
     'Class'=>$Class,
     'Order'=>$Order,
     'Family'=>$Family,
     'Genus'=>$Genus,
     'MyURL'=>$MyURL
    ));
    It basically says "Match the data in gz_groups2.Taxon where Taxon = the page URL...or where Taxon = the page URL's parent, grandparent, great grandparent, etc." (The table gz_groups1 simply includes more information about the groups, including links to each group's home page.)

    It works (so far), but it looks horribly amateurish to me. Can anyone suggest a better way of doing this?

    Thanks.

  2. #2
    From Italy with love silver trophybronze trophy
    guido2004's Avatar
    Join Date
    Sep 2004
    Posts
    9,500
    Mentioned
    163 Post(s)
    Tagged
    4 Thread(s)
    Please read this article from r937: http://sqllessons.com/categories.html

    If you still have questions after reading, please feel free to ask them.

  3. #3
    SitePoint Addict
    Join Date
    Jan 2012
    Posts
    261
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks.


Tags for this Thread

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
  •