php-windows Digest 15 Apr 2009 07:58:16 -0000 Issue 3604
Topics (messages 29264 through 29266):
Re: Need help updating a whole column in one table from another table
29264 by: Bill Mudry
29265 by: Bill Mudry
Re: php-win-which-orm-tool-for-windows-with.html
29266 by: Richard Quadling
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 ---
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
--- End Message ---
--- Begin Message ---
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
--- End Message ---
--- Begin Message ---
2009/4/14 Alvaro Carrasco <alv...@fractaldata.net>:
> Hi Richard,
>
> Sorry about not sending to the list, I found this post while browsing the
> internet and i'm not subscribed to the php-windows list.
>
>> 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'm the main developer of Outlet ORM and I currently use it with SQL Server
> 2005. Outlet relies on PDO and I use the PDO_ODBC driver when connecting to
> SQL Server from windows. Here is the link to the project:
>
> http://www.outlet-orm.org
>
>> 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?
>
>
> I'm planning on wrapping the new 2005 driver with a PDO-interface. In case
> you're interested, I'll post my progress to the outlet mailing list at:
> http://groups.google.com/group/outlet-orm
>
>
>>
>> Any help/suggestions would be appreciated.
>>
>> Regards,
>>
>> Richard Quadling.
>
> Feel free to redistribute this email if you want.
>
>
> Alvaro Carrasco
>
>
Thank you to Alvaro for this.
Alvaro, will you be able to submit your wrapper to PHP so that it can
become part of the core release? Currently, for VC9, there is no
php_mssql or php_pdo_mssql (php-5.3-win32-VC9-x86-latest as at
2009/04/15 04:50)
A replacement is needed.
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 ---
