Re: Fwd: Re: Need help getting syntax correct on 2 related problems.

--=====================_326205329==.ALT
Content-Type: text/plain; charset="us-ascii"; format=flowed


Neil, this message is getting posted to you on the forum because I tried to
send it to what I thought was your address of from your
message header but it bounced.




=========================================================
Niel,
I could have addressed this to the forum, but you were the only person who
provided any insight.

I used the script you gave to see what indexes the target table has
and got this
result:

Table Non_unique Key_name Column_name
species_backup 0 PRIMARY speciesID
species_backup 0 species_name species_name

The primary key is to be expected --- but it is not referred to in the script.
The species_name certainly is referred to but in read only. There are no writes
to it, only to the genus_name column. This leaves me, Niel, with no
understanding
what the script is complaining about so much.

The script runs ok (dropping the INSERT INTO LINE) just to separate the genera
names out of the species names and show them on the screen, which makes me
believe that code is properly written. This makes me think that
whatever the real
problem is, IT is not just a beginner's or novice error. There a
constraint to allow me
to input new species from input lists to avoid new duplicates to form
but we are not
(as said) writing to species_name, only reading.

Has showing the indexes helped? I have shown how the genera names can
be extracted
from each
species name and all that has to be done now is to be able to write
all these genera
into the genus_name column.

Do you have a solution in mind? How close to proper code had a got?
Does my script
come close or do you have a very different approach?
I am holding faith that I can't be far off.

I have left the prior message content on just in case you wish to
review what had already
been written.

Season's greetings,

Bill




At 11:59 AM 12/18/2010, you wrote:

>>X-Apparently-To: billmudry@rogers.com via 206.190.37.195; Fri, 17
>>Dec 2010 21:02:40 -0800
>>X-YMailISG: VXcUkx0cZAoaZQ_Oi5Gu3EM8cyrPz3YfpLGtdhzYnZbweC1w
>> 0RJyzOEmyD6JG_wC2rr3p37RqLblFWtY.C.ifFyb_rKF9cnevQQ7_ZGiuyEb
>> VIZRhgryz.NTWtJs3AVL5F9O2JzNa2Qgf2_4vZt1pwGSq2rkhIQGflpeLhM0
>> 4S.t9LFTr5NfPj45CyAHK13tW7fkXtJ3HSqlzymIgcjsi5xZ2HvndpqTj5pe
>> bKzo7QdyUub97AsW5yoNgH8MCuwhP7emjnWP2LHyJ9UjoTg4Su8pNxIam.Nt
>> RkMtHDJoU7RiVEZF8Ds2z4GmzBFlwtm1AZlvfYg1bgY8Gom1DjUH_osVE8c0
>> BIx.LHmzcyFW4g3Ya39wJ6nbw8jEbNeR7uUsmu2bRd8uO5z9duLrdqAydZto
>> 53BCRjIU3ZSbOnejK7O6IbwKKzbQJ7Cs6WORuY6WdVy.5._Pv8KV0SygHn1X
>> ZH_3X7FBx5UAO2WFBBAbRLHoa_B2RcRIbrMGvs5VwyGfJoXeLJRkTsjqKmkZ
>> ZYikr4gQGU.adAzr3Et3OrcOq8KwilqfEIt8wEye1h.6StfFKS0t6fsJU3nE
>> W2Z30o4Kj2PDQhcidHAZQUqp_91iaKchjySiyKtuB0gBugr50.OmKw--
>>X-Originating-IP: [76.75.200.58]
>>Authentication-Results:
>>mta1012.rog.mail.sp1.yahoo.com from=chance.now; domainkeys=neutral
>>(no sig); from=chance.now; dkim=neutral (no sig)
>>X-Host-Fingerprint: 76.75.200.58 pb1.pair.com
>>Mailing-List: contact php-windows-help@lists.php.net; run by ezmlm
>>list-help: < mailto:php-windows-help@lists.php.net>
>>list-unsubscribe: < mailto:php-windows-unsubscribe@lists.php.net>
>>list-post: < mailto:php-windows@lists.php.net>
>>List-Id: php-windows.lists.php.net
>>Delivered-To: mailing list php-windows@lists.php.net
>>Authentication-Results: pb1.pair.com
>>smtp.mail=spam-free@blueyonder.co.uk; spf=pass; sender-id=pass
>>Authentication-Results: pb1.pair.com header.from=not@chance.now;
>>sender-id=unknown
>>X-PHP-List-Original-Sender: spam-free@blueyonder.co.uk
>>X-Host-Fingerprint: 195.188.213.6 smtp-out3.blueyonder.co.uk Linux
>>2.5 (sometimes 2.4) (4)
>>Date: Sat, 18 Dec 2010 05:01:08 GMT
>>From: Niel Archer
>>To: php-windows@lists.php.net
>>Reply-To: php-windows@lists.php.net
>>X-Mailer: Becky! ver. 2.48.02 [en]
>>Subject: Re: [PHP-WIN] Need help getting syntax correct on 2
>>related problems.
>>
>> > 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

--=====================_326205329==.ALT--