Need Help Travelling up a Relational Tree

[billmudry]

On average, I Have been doing quite well on my "TAXA" Wood Wood Knowledge Base website in getting many of the features coded and working --- more than this slow
and amateur PHP coder that he could. I even got a search engine working yesterday,
something I was not sure that I could do. Arranging travel up the botanical tree
is one of the major tasks yet to be conquered in the whole plan for the site.

To help anyone to visualize what this is all like, I will include the URL for the site: http://www.prowebcanada/taxa

There are 4 entry points to the tree. If you have had any botanical training ever, you will recognize what these are (all to do with botanical taxons). There are 4 levels connected by three one-to-many relationships:
Order -----> Families
Family ----> Genera
Genus -----> Species
(i.e. - A hierarchical tree)

Once you enter into this botanical tree at any of these levels, traveling down the tree all the way to details on one wood (one species) works well (amen!).

Now I want to complete being able to go the other direction UP the tree. This means for any species, genus or family, finding the parent of that element. I suspect that this should be a fairly standard solution requiring a join between the present table and the parent table. I keep getting error including fatal errors whenever I try to craft the right SQL statement that will do this. My apologies for being so inept at this.

If I can get help just creating ONE of the three solutions, I am sure that I can get the remaining two to work. Lets choose going from a page that is displaying a family to be able to find its parent order. Here are some basic facts to guide you:
The database is called 'taxa"

The PHP file that displays each family
(our upward start point) is 'showfamilies.php'

Family data is stored in "sci_family"
Family names are stored in column "family_name".
We need to find what order is the parent for any one family being displayed.
The local key to equate from is column 'orderID'

The orders are displayed in file 'showorder.php'
but order data is stored in table 'sci_order'
This file also has a prime key (foreign key) of 'orderID"

Once I can have code to find the parent, there are two things I will want to do
with it. First, I simply will display the order parent on the family page. Secondly, I should have little trouble or none, crafting a link to travel UP the
tree to take the user to the display page for that parent order.

Later, after replicating the algorithm to the other two remaining relationships it should not be long before total travel up and down the tree will finally be complete. I have tried working on the required SQL statement but always come up
with errors. That is especially the help I need. Being able to travel both directions is one of the key functions planned for this knowledge tree.

I trust I have included the parameters and names you will need. I could provide the entire schema for both MySQL files ... but that really should not be needed and could even cloud and complicate thinking. I look forward to help from the group with thanks even in advance.

... Eagerly waiting for code suggestions,

Bill Mudry
Mississauga, Ontario Canada
(billmudry at rogers.com should you care to email me).

[whiteyoh]

hi,

without reading everything it seems to me like your wanting to find suitable ways to travel up and down the taxonomy of whatever you have created.

Are you using foreign keys? and are you familiar with something called "third normal form" by a chap called boyce codd?

[mschroeder]

Bill,

Having little if any biology background, can you tell does your relational tree have multiple parents like a family tree?

[A]   [B]
      |_____|
         | 
   -------------
   |     |     |
  [C]   [D]   [E]

or does a tree only have one parent in your use case?

[A]
       |
  ------------
  |    |     |
 [B]  [C]   [D]

[billmudry]

All relationships in the TAXA wood tree are one to many. To have more than one parent for any of the children would be a violation of the whole effort of showing how all plants in the world are ultimately related to each other.

My hardest part for me is getting the right SELECT ....... JOIN ...... statement working that will dig out the parent for amy child (eg. the parent Order for a
chosen Family under that Order (or the parent Family of a genus or a parent genus
of a species). I just find joins hard to do without ending up with hard to handle errors.

[whiteyoh]

you may not need a select join. you could use foreign keys for each table. just include an additional column in your table that holds the id of what it is related to.

[billmudry]

I learned about database normalization as far back as 1985. I have not specifically heard before of third normal form" by boyce codd. However, if you look typically how the website is used, you can now travel down the one to many relationships but so far no convenience to travel upward. The end result has to be in terms of a combination of PHP and SQL statements. I know that because the answer is always in another table in a higher level of the tree, I will very likely need to use a Select ...... Join ...... statement. I just seem to be poor at getting the right statement.

To me, there are pros and cons to high level normalization. I could normalize the
overall schema I am using more so than it is but storage is so cheap now that sometimes a minor amount of duplication is a helpful convenience. I remember clearly when storage space was a dear commuodity. My species display page in particular is simpler than if all the data was fractionated into a longer list of tables.
I am not sure how you would feel further normalization would help when the relationship of a parent order from a child family, a parent family from a child
genus and a parent genus from a child species are all well defined. All other columns except perhaps index columns aren't even needed for the answers I am seeking.

At one point, I had the relationship defined by constraints. I found they only got very much in the way as I tried to set up more data in the tables, so I took them out. There is only one 'unique' constraint on the species table so that I could import new species from long lists. That worked beautifully. In one large import,
it avoided the new formation of over 1,600 duplicates(!).

If you would read more carefully instead of just skim reading, you will see that I took care to include all table and column names needed to come up with an answer.
It may seem a bit longer but that is so there should be everything needed to fully understand how to arrive at the actual code that will do the job.

[billmudry]

whiteyoh, how convenient that you happen to be online right now :-).
I already have columns for primary and foreign keys in the tables. As per my words just a while ago, when I did apply a full set of constraints, they became a ROYAL
PAIN instead of being a help. It stopped me from adding any new data using PHP or MySQL-front, frustrating enough that I took them all out. They stopped me cold from adding new data.

I would not be aware of how that would simplify the code I need to find what the parent in each relationship would be. How would the code I need look different compared to using JOIN statements?

[mschroeder]

I think the information provided by mysql on this topic will be of use:
http://dev.mysql.com/tech-resources/articles/hierarchical-data.html