Prev: Installing community PHP on Windows through WebPI
Next: php-win-which-orm-tool-for-windows-with.html
From: Bill Mudry on 14 Apr 2009 19:35

>>
>>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
From: Niel Archer on 15 Apr 2009 16:05

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

First  |  Prev  | 
Pages: 1 2
Prev: Installing community PHP on Windows through WebPI
Next: php-win-which-orm-tool-for-windows-with.html