At 10:40 AM 4/14/2009, you wrote:
> In the botanical tree of woods, I got the original code for working
> ok a few days ago but there are so
> many errors in the Species MySQL file that there is a lot of cleanup
> in the data needed before the
> whole tree will work well.
>
> In particular, there are two files of interest that would be needed
when you say "file" do you mean a MySQL table
Yes. Although there are additional tables for higher organization
levels (sci_family for the families level and sci_order
for the top order level), these are not needed The database name is
called 'taxa'. The only two (MySQL) data tables
that are needed for what I am trying to do are 'sci_genera' for the
genus level of botanical organization and 'species' for the
lowest level in the botanical tree. For every woody genus there are
many woody species under each genus (many-to-one).
Somehow there are just zeros in the genus ID field in the species
table when there should be the correct number key for each
corresponding parent genus instead. I went through all the species
starting with the letter A to manually look up the genus
ID number via the "generaID" field in sci_genera and copying it into
the generaID field in the species table. That is so time
consuming though (hours of work for letter A alone) when it should be
easy to do the same automatically in minutes once
the rigtht script is run.
> to correct a lot, namely 'sci_genera'
> and 'species'. Any one genus ( the oddly plural name for genera) form
> a one to many relationship of
> species. There are far more species, therefore, than genera.
> (Actually, I made a duplicate species2
> file to try this on first so I don't destroy the original accidently
> until I prove it works).
>
> In the species file, I found that the botanical tree is
> (understandably) not working well because almost all
> of the entries of all species for generaID were set to zero instead
> of the proper numeric index for the
> genus each should map to. We are talking over 6,500 records here, so
> hand updating is a time consuming
> and tiring job to update this column manually. Being told about
> MySQL_Front was a hugh jump forward
> in efficiency in doing this but even slow as a turtle compared if I
> can get a program to search for the proper
> genus ID for each species, insert it where the zero's are now and
> automate the process.
>
> I tried various INSERT statements suggested on the Net but nothing
If the data is already in the tables, but incorrect, you need to be
doing UPDATEs not INSERTs
A field called generaID exists in both the sci_genera and species
table. I should read up later
(got work to do during business hours) on the differences between
UPDATE and INSERT.
The zeros in the generaID field in the species table needs to be
totally overwritten by the proper
values from the generaID field in the sci_genera table.
You cannot just copy the whole field as it is in the sci_genera table
to the species table because
of the many-to-one relationship of genera and species. There will be
multiple insertions of the same
generaID for as many species that are classified under each genus. As
explained before, a species
name by its very nature has in it the genus name it is under and can
be separated out easily as a
substring.
> quite worked (.... ie. I did make multiple
> efforts and research) but I am still relatively a young amateur to
> PHP with little experience in correcting data
> in one file from another. One important fact to doing this did work:
> - Any species name has two parts to it, the genus name as
> the first part and the species name (also
> called the 'epithet' for the second part separated by a
> space. For example,
> Quercus rubra - is the name of red oak.
> Quercus is the genus name for all oaks.
> ' rubra' nails this down to
> exactly and only one type of oak.
> Dalbergia nigra - is Brazilian Rosewood.
> Dalbergia is the genus for all rosewoods.
> 'nigra' nails this down to
> the one specific rosewood.
> Using:
>
> $spacepos = stripos($sciName, " "); //genus in species full
> name is at first space after first string character
Where is $sciName coming from? Is it the result of getting data from a
MySQL table?
$sciName is the field for storing all the botanical name of woods in table
'species'. The data (ie. botanical names of woods) is not from some other
table at all since it is primary data entered through my (and others) research
into what plants are wood or woody and was directly entered into that field
over the past 7 years.
> //echo "\$spacepos is - $spacepos<BR>\n"; //debug statement to
> prove this works
> $genera_name = substr($sciName,0,$spacepos); //The genus name
> is from the first character to the first space.
If there is only a single space, you could also have done:
$name = explode($sciName, ' ');
As I understand, 'explode' takes each field name and changes it into
an array. It
does *not* split a name within a field. Each botanical species name
is not straddling
across two fields but both the genus part and the second part of any
species name
all reside *together* in the same field. I gave some examples. Each
record is a
two word string in the same field. It requires string manipulation to
split out the genus
name.
This would give you an array with the genus in $name[0] and other part in
$name[1]
I can often be shown to be wrong but I have never seen 'explode' used to
split a string apart within the same field, only separating *whole* multiple
field data into arrays.
>
> made it easy to extract each genus name from every species. I was
> even able to list all the resulting genus names using
> a while loop. That part worked.
>
> Now comes the part I am struggling with. For each genus extracted
> from each species name, it needs to search in
> the sci_genera MySQL file for the corresponding same genus name under
> field 'genusname' and then read what
> the genus ID for that genus is under column 'generaID' and then write
> that to the 'generaID' field in the species file,
> replacing the zero value.
>
> When I can finally get this to work, I will be much rewarded:
> - The species data will have undergone over 6,000
> corrections in minutes instead of hours and hours of
> manual correcting.
> - The newly crafted botanical tree will be far closer to
> working all the way down from 'order' level to especially
> displaying details on each species.
> - I will have learned some valuable lessons on updating a
> file from another file.
>
> So I hope those of you who are way past this level of coding will be
> patient in tolerating my inquiry. There is so much
> else I want to work on that I look forward to licking this problem
> effectively and going on. I suspect this should not be
> anywhere a challenge to many of you that, at this stage, it is
> proving to be for me.
>
> I believe I did put enough minimal information on files and field
> names but let me know if there are other facts you
> need.
If you are dealing with MySQL tables, we need the table layout. It
I was careful to try to simplify the problem by *not* bothering to list
all the fields since only the ones I have listed are needed to get the
job done. All fields have to still line up for each record as before,
after the task is done, but aside from that, their data is not involved.
I risk introducing possibly more confusion than clarity by listiing more
information than is actually needed. What you do need to know is:
Database name - Taxa (ie. short nickname for taxonomy)
.................................................................................................................
Species table name - species (---- with a practice copy also of species2).
Species field name - sciName (which inherently also has the genus name
by international definition of how species
are named)
Genus ID name in the Species file - generaID
.................................................................................................................
Genera table name - sci_genera
Genera names in genera table -genus name
Genus ID key name - also named generaID
.................................................................................................................
NB. - all other fields are just along for the ride. If you still want the whole
structure listed, I can provide it but it can take up a lot of room
when they do
not really relevant for just looking up each proper genus ID in the genus file
as needed by each species and copying it over to the genus ID file in the
species table.
appears to me from what you are describing that this could be done with
SQL alone. Even if that is not the case, knowing the layout of the
Indeed, that is probably true. I looked up various cases on the Net and
tried them but none worked properly. That is what drove me to ask.
tables will make it simpler to suggest PHP to alter the contents of
those table. Can you supply the CREATE statements so we can see the
layout. A couple of example rows may also be useful
The original files were made years ago, not recently, so that would be a lot
of work to recreate such CREATE statements for a task that should prove
to require a simple solution. Would that not be overkill?
Except for the tables and fields I already have listed, knowledge of details
of all the others just are not needed for the task at hand. We would be in
error if their data or relative positions to each other shifted. Actually, out
of the 4 fields in two tables I listed, only generaID in the species table
(now all zeros) should change. the data in the other three are ok.
> To those of you who celebrate Easter, a slightly belated Happy
Easter to you.
>
> Thank you for your help in advance,
>
> Bill Mudry,
> Mississauga, Ontario, Canada
> (Next to Toronto).
>
I trust this clarifies a lot. The final solution should prove to be
quite simple.
Bill Mudry
--
Niel Archer
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
