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
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
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
//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.
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.
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).
