Building a mysql update from an input
- I just started playing with mysql/php. Very neat stuff. I used phpmyadmin
to output a database table. I have (before I probably understand what I am
doing) taken the output and made changes that I now want to load back into
the table. What I need to do is take my lines I received when outputing
from the table -- called starting point in the following explanation --,
modify them (with a clip I presume) -- so that they are like the ending
point reflected below -- and then I can update my datatable.
I have tried getting the steps necessary put down into a stepwise
progression list. I figure the first part will be done with a search and
replace, but after that .... I will be studying. Any thoughts on any step
would be appreciated as I try to build this thing.
UPDATE Organizations VALUES (20, 'Organization Name', 'Organization
Address', 'Organization City', 'ST', '99999', 'USA', '555-555-5555',
'email@...', Contact Name, 'Comments about the
UPDATE Organizations SET organization='Organization Name',
addr='Organization Address', city='Organization City', state='ST',
zip='99999', homecountry='USA', phone='555-555-5555', fax='555-555-5555',
email='email@...', contact='Contact Name',
description='Comments about the Site.' WHERE organization_id=20;
List of fields (in this example):
organization_id -- use this after where
organization -- use these sequentially after set
(question: I assume I need to load these into an array so that I can get
them back when I need them -- note for the next table I work on, the fields
list will have a different number of items and different names)
Steps to get from Starting Point to Ending Point
1. change "INSERT" into "UPDATE" (question: I assume I will do this all one
line at a time).
2. change "VALUES" to "SET"
3. take the primary key (in this example it is 20) to end (replace the ")"
with it) and add "WHERE *FIELD*=20
*FIELD* is the primary key field which is the first field in the list of
fields above (here organization_id) (question: is it better to load this to
a variable and not move cursor instead waiting until I get to the end of the
4. Return cursor to "(" (unless I went with the variable thought above)
5. Replace "(" with *FIELD*="
*FIELD* to come from second item in list of fields for this table (here
6. find and replace each successive ', ' with ', *FIELD*=' where *FIELD* is
the next successive item in the field list starting with number three until
7. I suppose we should report error if the number of field items does not
match the number of ', ' instances.
8. move to next line if there is one and repeat else terminate