> 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?
You have an index (at least two) on columns in your table. At least one
of them specifies that contents of the column must be unique (presumably
on the 'genus_name' column as it's the only one changed). The server is
complaining that a second, identical value is being added.
SHOW INDEX FROM species_backup
will show you information on the indexes for your table. The index on
genus_name has to have the 'non_unique' column set to 1/true.
> 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
>
>
>
>
>
>
--
Niel Archer
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
