php-windows Digest 14 Apr 2009 18:37:17 -0000 Issue 3603
Topics (messages 29260 through 29263):
Which ORM tool for Windows with Microsoft SQL Server 7/2000/2005.
29260 by: Richard Quadling
Re: Need help updating a whole column in one table from another table
29261 by: Niel Archer
29262 by: Bill Mudry
29263 by: Niel Archer
Administrivia:
To subscribe to the digest, e-mail:
php-windows-digest-subscr...@lists.php.net
To unsubscribe from the digest, e-mail:
php-windows-digest-unsubscr...@lists.php.net
To post to the list, e-mail:
php-wind...@lists.php.net
----------------------------------------------------------------------
--- Begin Message ---
Hi.
I'm trying to find an ORM tool to allow me to talk to Microsoft SQL
servers (V7, 2000 and 2005) for Windows.
For those that use ORM, what do you use?
I've looked at Doctrine (requires php_pdo_mssql which isn't part of
the VC9 win32 builds; for the VC6 builds, it requires ntwdblib.dll
which is oh so dead).
I'm looking into Propel next. This now seems to be using PDO and
PDO_MSSQL, so it seems I've got the same problems.
Ideally, using the nice shiny Microsoft SQL Server 2005 Driver for PHP
from Microsoft (http://www.codeplex.com/SQL2K5PHP) would be nice. But
this isn't PDO.
Is there anyone capable of getting the Microsoft driver PDO aware?
Any help/suggestions would be appreciated.
Regards,
Richard Quadling.
--
-----
Richard Quadling
Zend Certified Engineer : http://zend.com/zce.php?c=ZEND002498&r=213474731
"Standing on the shoulders of some very clever giants!"
--- End Message ---
--- Begin Message ---
> 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
--- End Message ---
--- Begin Message ---
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<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, ' ');
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
--- End Message ---
--- Begin Message ---
> 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
--- End Message ---
