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...
$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");
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?