I'm sorry, I don't know exactly what you mean by "definitions." The fields Taxon and Parent are both varchar, with a 50-character limit. ParentID is int(1).
Here's a discussion that describes the table in a little more detail -- http://stackoverflow.com/questions/33248361/hierarchical-query-in-mysql-ii And this is the discussion where someone suggested I check out PostgreSQL -- http://stackoverflow.com/questions/33313021/displaying-simple-counts-from-stored-procedure On Sun, Oct 25, 2015 at 10:59 AM, Adrian Klaver <adrian.kla...@aklaver.com> wrote: > On 10/25/2015 08:48 AM, David Blomstrom wrote: > >> I'm creating a website focusing on living things (mostly animals). I >> have multiple huge MySQL database tables with animal taxons arranged in >> a parent-child relationship. I was trying to figure out how I could >> navigate to a URL like MySite/life/mammals and display the number of >> children (i.e. orders), grandchildren (families), great grandchildren >> (genera) and great great grand children (species). >> >> I was then steered towards some sort of MySQL substitute for a full >> outer join (which can apparently only be done in Postgre), followed by >> an introduction to stored procedures. Pretty complicated stuff. >> >> Then someone told me it's stupid to jump through all those hoops when >> you can easily do that sort of thing with Postgre. >> >> So that's my specific goal - to set up my animals website so it can >> quickly and efficiently calculate and display things like grandchildren, >> great grandparents, the number of children that are extinct, etc. >> >> My database tables look something like this, where Taxon, Parent and >> ParentID are the names of the key fields: >> >> Taxon | Parent | ParentID >> Animalia | Life | (NULL) >> Chordata | Animalia | (NULL) >> Animalia | Chordata | 0 >> Mammalia | Animalia | 1 >> Carnivora | Mammalia | 2 >> Felidae | Carnivora | 3 >> Panthera | Felidae | 2 >> Panthera-leo | Panthera | 1 >> Panthera-tirgis | Panthera | 1 >> > > I am not entirely following the above. Could you post the actual table > definitions? > > > >> Is that table structure sufficient for PostgreSQL to calculate >> grand-children, etc., or will I have to modify it? I think the key words >> are "hierarchical query" and/or "nested set." There's a popular tutorial >> (though I can't find it at the moment) that illustrates the procedure, >> which involves creating TWO numerical fields - a process that I think >> would be overwhelming when working with over 50,000 taxonomic names. >> >> So that's my question; can I do all this recursive stuff in Postgre with >> the table structure posted above, or will I still have to add a second >> numerical column (or otherwise my table)? >> >> >> >> > > -- > Adrian Klaver > adrian.kla...@aklaver.com > -- David Blomstrom Writer & Web Designer (Mac, M$ & Linux) www.geobop.org
