Need help updating a whole column in one table from another table

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

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


--=====================_94335234==.ALT--

Re: Need help updating a whole column in one table from another table

> 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
\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

Re: Need help updating a whole column in one table from another table

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

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
\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

--=====================_136678046==.ALT--

Re: Need help updating a whole column in one table from another table

> 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
\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

Re: Need help updating a whole column in one table from another table

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

At 02:37 PM 4/14/2009, you wrote:
> > (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.

Then it must work on a created field that has not been populated yet?
It doesn't
also create a field, does it?




> $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.

Oops. Sorry, That should have been sciName for the name of the field,
not $sciName. I got too used to it becoming $sciName later.


> > >If there is only a single space, you could also have done:
> > >
> > >$name = explode($sciName, ' ');
>
>Argggh! Got the syntax wrong.

(some older lines taken out for brevity)


>Given the foillowing:
>$pieces = explode(' ', 'one two three');
>
>$pieces will be an array containing
>[0] => 'one'
>[1] => 'two'
>[2] => 'three'

Oh! That is interesting. So you can "explode" parts of a string, too, into
separate words? That almost suggests a multidimensional array by the
time you do this for all species in the database. I had thought that it works
only in dividing up a record into an array by its field contents.


>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]

........ so $name[0] would have the current genus name, right?

> >
> > 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

--=====================_161162281==.ALT--

Re: Need help updating a whole column in one table from another table

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


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

Our messages got long enough (along with the abstraction of supper ;-) )
that I forgot to mention that I tried to run it in a SQL query window in
phpmyadmin. I got the following error instead:
#1054 - Unknown column 's.generalID' in 'field list'
It must need some minor tweeking yet.

This script is at an SQL level past my own understanding so far. If we get it
to work yet, it should be quite the instructive lesson for me. I still stand to
gain hours of otherwise hand correcting when it finally works.

With thanks to this point,

Bill Mudry
(Mississauga, ON Canada)






>>--
>>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

--=====================_161162312==.ALT--

Re: Need help updating a whole column in one table fromanother table

Bill Mudry wrote:
>
>>>
>>> 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.
>
> Our messages got long enough (along with the abstraction of supper ;-) )
> that I forgot to mention that I tried to run it in a SQL query window in
> phpmyadmin. I got the following error instead:
> #1054 - Unknown column 's.generalID' in 'field list'
> It must need some minor tweeking yet.
>
> This script is at an SQL level past my own understanding so far. If we
> get it
> to work yet, it should be quite the instructive lesson for me. I still
> stand to
> gain hours of otherwise hand correcting when it finally works.
>
> With thanks to this point,
>
> Bill Mudry
> (Mississauga, ON Canada)
>
>
>
>
>
Bill,

I was reading over this thread this morning and I think it would be
helpful to everyone watching it if you could post the structure of your
db. You mentioned phpMyAdmin. From there simply run the 'DESCRIBE
sci_genera;'. Go to the print view for the results and you can copy and
paste it into an email. Do the same for the species table.

Cheers,
James

--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Re: Need help updating a whole column in one table from another table

> > > (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.
>
> Then it must work on a created field that has not been populated yet?
> It doesn't
> also create a field, does it?

INSERT inserts a new row. i.e. all of the fields for the table, using
default values where none are supplied. A row can't exist with
unpopulated fields, they would have values even if the value was NULL
(which should be read as value unknown)




> > > >This would give you an array with the genus in $name[0] and other part in
> > > >$name[1]
>
> ....... so $name[0] would have the current genus name, right?

In that example, yes.


> >>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.
>
> Our messages got long enough (along with the abstraction of supper ;-) )
> that I forgot to mention that I tried to run it in a SQL query window in
> phpmyadmin. I got the following error instead:
> #1054 - Unknown column 's.generalID' in 'field list'
> It must need some minor tweeking yet.

The error is indicating a column is misnamed, this may be due to a
simple typo or a misunderstanding by one of us.
At this point I have to agree with James' suggestion. Use phpMyAdmin to
supply the actual table info.

> I was reading over this thread this morning and I think it would be
> helpful to everyone watching it if you could post the structure of your
> db. You mentioned phpMyAdmin. From there simply run the 'DESCRIBE
> sci_genera;'. Go to the print view for the results and you can copy and
> paste it into an email. Do the same for the species table.
>
> Cheers,
> James

Good database design is pretty essential to using a Db, wether from PHP
or any other language. From my understanding of your Db so far I think
you might benefit from some input on Db design. For example, naming the
"generalID" column in your sci_genera table is somewhat misleading. It
would be more normal to call it simply 'id' and use 'genusID' or
'genus_id' as the reference to it in the species table. However, that
is a discussion more suited to the PHP database list.



> This script is at an SQL level past my own understanding so far. If we get it
> to work yet, it should be quite the instructive lesson for me. I still stand to
> gain hours of otherwise hand correcting when it finally works.
>
> With thanks to this point,
>


--
PHP Windows Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php