I have worked up an SQL script that needs expansion into two
different scripts to take care of two different needs. They can be
worked from one table, the 'species' file while the second will
probably need the species file to have a join to the sci_genus file.
First, perhaps I should put a few words in for those that have never
been exposed to botanical species and genera. All living things are
given botanical names by scientific convention. Each unique living
thing (from bacteria to fish to animals, plants and trees) are assigned
a unique two part name called the species name. Wood comes from
trees (of course) so the wood is also referred often by the tree's
scientific name.
The first word in a species name happens to be the name of the next
and higher organizational level above it, the genus. If you have a species
name, you are therefore able to extract the genus name. (genus is singular,
genera is plural, species is oddly singular and plural all the time
by the way). Here are some examples:
Species Name Genus name
Abies Alba Abies
Acacia farnesiana Acacia
Caesalpinia ebano Caesalpinia
Dalbergia oliveri Dalbergia
Santalum album Santalum
Problem 1
My pet project, a wood knowledge base is reporting over 15,000
botanical names of woods from
around the world. The database is called 'taxa'. information on all
wood species is kept in a table
called 'species'. Within the 18 fields in that one file, two relevant
fields included are 'species_name'
(which records all scientific species names and 'genus_name' which
holds all genus names.
Note again that from the same file (no JOIN needed), every genus name
can be read inside each
species name (as above). Unfortunately, the genera (plural of genus)
column is missing 1/4 or
more of the names it should have for every record of a species (
genera to species is a one to
many relationship). To extract all the needed data to insert to the
blank cells, I was able to put
together the following script (using phpadmin) that lists nicely on
the screen all the genera from
extracting them from all the species names:
SELECT LEFT(`species_name`, LOCATE(' ', `species_name`)-1)
FROM species_backup
WHERE `genus_name` IS NULL
note --- I used a copy of species I names species_backup to protect
the original file while
experimenting to see what will work out.
So far so good. Now all I should have to do is have the proper syntax
for a INSERT INTO
statement to write all the missing genera names and repair a fair
size chunk of errors (missing
data). Here is what I tried;
INSERT INTO species_backup ('genus_name')
SELECT LEFT(`species_name`, LOCATE(' ', `species_name`)-1)
FROM species_backup
WHERE `genus_name` IS NULL
I got the following error: #1062 - Duplicate entry '' for key 2
What did I do wrong? I must be close. What is the proper syntax that will work?
Once this runs ok, the missing data problem will be sold. I may,
though, work it
later into a short PHP script pointed to by a menu link for
occasional maintenance
of the website and its data.
Problem 2
Go to http://www.prowebcanada.com/taxa/ and choose Woody Orders from
the menu on the left.
It will show all the Orders that I have found have woody elements in
them. Pick one and
you will travel down the botanical tree to show the data sheet on the
chosen order.
Below that on the page will be all the woody Families that belong
under the chosen Order.
Choose one and .... we continue to work our way down the botanical
tree further until
a chosen Genus data page lists all woody Species under a Genus, we
pick one and end up
at the bottom of the tree and the data page for the chosen Species.
In other words, downward travel in the tree works quite well. After
some prior attempts,
I want to also get the upward path of child to parent relationships
finally working and
finished). There are three paths needed to complete the entire upward
travel means:
Child to Parent
Species to Genus
Genus to Family
Family to Order
The last two I am quite sure will need a JOIN statement between the
relevant child table
and the parent table while, as shown above, the genus name can be
extracted directly out
of the species name. That should be easier. Lets leave the last two
for some other day.
If you are on a species data page, the parameter species_name will be
in memory since
it had to be chosen at some stage to get there. A typical sample of
the link from a species listing to the one chosen species can be seen
by resting a mouse on one choice in the
listing of species, (using a GET statement) such as:
http://www.prowebcanada.com/taxa/displayspecies.php?species_name=Acacia
albida
The above first script works well to list ALL genera, but all I need
is the parent genus
for the child (for example above, the parent Genus for Acacia
albida). So what kind of statement can best do that? Once this works,
I can place it in a link statement so it is
possible to move not only down the tree but for the first time UP the
botanical tree one
level to a parent genus. (.... leaving only two more paths to develop
for complete upward
mobility).
I have an uncomfortable feeling that this should be a rather
elementary part of learning
PHP and MySQL. Please bear with me if it is. I have tried
combinations earlier this year
only to be faced with errors I could not handle.
You may wish to separate these two tasks into separate responses for ease of
discussion and handling.
These are two problems left of a short list of tasks in the project
before I can lay coding aside for a few months to concentrate on a
massive, huge amount of data input. It would
be nice if I got them all done as soon as possible to start the year
with data input instead.
Your help(s) always much appreciated,
Season's greetings and Merry Christmas,
Bill Mudry
Mississauga, Ontario Canada
