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
