> 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
