> 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.
INSERT creates a new entry, update changes an existing ones. So you
cannot use INSERT to corect your data.
> 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
yup, understood
> 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
Not really. $sciName is a variable in PHP. I was trying to ascertain
where/how it gets populated. I'm guessing a read from the table as part
of a loop.
> '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, ' ');
Argggh! Got the syntax wrong.
> 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.
Given the foillowing:
$pieces = explode(' ', 'one two three');
$pieces will be an array containing
[0] => 'one'
[1] => 'two'
[2] => 'three'
the string has been exploded into fragments using the supplied character
(in this case a space) as the break point. This is similar to what you
were doing, but a single function call.
>
>
> >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?
I meant the SQL statement used to create the database/tables. Something
like
CREATE TABLE `sci_genera` (
`generalID` int(11) NOT NULL auto_increment,
`genus_name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `genus` (`genus`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
which is how I guess your sci_genera table would be created with just
the fields you described.
This contains a lot of useful info about your structure without you
having to explain it yourself.
Assuming the above table structure and the following for species2
CREATE TABLE `species2` (
`generalID` int(11) NOT NULL,
`species_name` varchar(255) NOT NULL default '',
PRIMARY KEY (`species_name`),
KEY `genus_id` (`genusID`)
) ENGINE=MyISAM DEFAULT CHARSET=utf8
I used the two examples you originally provided to create two rows in
each table
sci_genera
ID genus_name
1 Quercus
2 Dalbergia
species2
ID species_name
0 Quercus rubra
0 Dalbergia nigra
The following SQL query
UPDATE species2 AS s LEFT JOIN sci_genera AS sg ON (sg.genus_name = LEFT(s.species_name, LOCATE(' ', s.species_name) - 1))
SET s.generalID = sg.generalID;
alters the contents of species2 to become
ID species_name
1 Quercus rubra
2 Dalbergia nigra
Assuming I understood your intent correctly, this should be the result
you are after.
This should fix your table. It does what you are trying with PHP, but
using SQL only. With indices on the relevant fields it will probably be
(much) faster too.
--
Niel Archer
niel.archer (at) blueyonder.co.uk
--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
