> 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 > 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 > 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? > //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, ' '); This would give you an array with the genus in $name[0] and other part in $name[1] > > 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 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 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 > 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). > -- Niel Archer -- PHP Windows Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
