Google Answers Logo
View Question
 
Q: MYSQL statement question. ( Answered 5 out of 5 stars,   0 Comments )
Question  
Subject: MYSQL statement question.
Category: Computers > Programming
Asked by: whirlco-ga
List Price: $10.00
Posted: 05 Nov 2002 16:18 PST
Expires: 05 Dec 2002 16:18 PST
Question ID: 99850
Assuming a mysql table --> 'contacts'. 
With rows --> 'firstname', 'lastname', 'age', 'location'.

Example:

CREATE TABLE (contacts
contact_id int(11) NOT NULL AUTO_INCREMENT;
firstname varchar(50);
lastname varchar(50);
age varchar (5);
location text;
PRIMARY KEY (contact_id)
);

How do you insert data into all rows using one mysql statement?

Example of what needs done.

 INSERT INTO contacts (firstname, lastname, age, location) VALUES
(Jim, Smith, 50, Ohio), (John, Jones, 45, Kansas), (Mary, Johnson, 30,
Iowa), (Helen, Thompson, 25, Texas) ......etc etc.

How is this accomplished?  What is the code needed to loop through the
database and insert the values.

FYI ~ actual database has many more columns and has nothing to do with
contact information :)
Answer  
Subject: Re: MYSQL statement question.
Answered By: sgtcory-ga on 05 Nov 2002 17:35 PST
Rated:5 out of 5 stars
 
Hello whirlco,

The quick answer is - you wouldn't do this with one insert command.
You would need to create an insert statement for each record, and the
resulting set of instructions can be considered as one sql statement.
I shortened the table row names so you can view the code on one line.

INSERT INTO contacts VALUES(na,lna,a,lo) VALUES
('Jim','Smith','50','Ohio');
INSERT INTO contacts VALUES(na,lna,a,lo) VALUES
('Tim','Jones','22','Alaska');
INSERT INTO contacts VALUES(na,lna,a,lo) VALUES
('Ron','Wales','95','Maine');
INSERT INTO contacts VALUES(na,lna,a,lo) VALUES
('Pat','Donut','18','Iowa');

You would keep going until you were done. Of further note, if you are
inserting a value (even if it is null) for each field that you have,
you can lose the initial values assignment, as the data falls into
place on it's own. Example:

INSERT INTO contacts VALUES ('Jim','Smith','50','Ohio');
INSERT INTO contacts VALUES ('Tim','Jones','22','Alaska');
INSERT INTO contacts VALUES ('Ron','Wales','95','Maine');
INSERT INTO contacts VALUES ('Pat','Donut','18','Iowa');

Null fields can be defined by simply using empty quotes. Example:

INSERT INTO contacts VALUES ('','Smith','50','Ohio');
INSERT INTO contacts VALUES ('Tim','','22','Alaska');
INSERT INTO contacts VALUES ('Ron','Wales','','Maine');
INSERT INTO contacts VALUES ('Pat','Donut','18','');

I really didn't use a search strategy to find this answer, as I run a
few websites with PHP/Mysql backends. There are many good reference
sites, and this page at the Mysql site is a good read:

http://www.mysql.com/doc/en/Loading_tables.html

It makes mention of the LOAD DATA statement, which really, to me makes
for just as much, if not more work on our ends. The industry standard
is exactly what you see above :-)

I hope this answers your question. Should you need clarification,
please ask before rating this answer, as I would love to offer more
assistance.

Thanks for the question!
SgtCory
whirlco-ga rated this answer:5 out of 5 stars
Thanks SgtCory. I appreciated the quick and accurate response.

Comments  
There are no comments at this time.

Important Disclaimer: Answers and comments provided on Google Answers are general information, and are not intended to substitute for informed professional medical, psychiatric, psychological, tax, legal, investment, accounting, or other professional advice. Google does not endorse, and expressly disclaims liability for any product, manufacturer, distributor, service or service provider mentioned or any opinion expressed in answers or comments. Please read carefully the Google Answers Terms of Service.

If you feel that you have found inappropriate content, please let us know by emailing us at answers-support@google.com with the question ID listed above. Thank you.
Search Google Answers for
Google Answers  


Google Home - Answers FAQ - Terms of Service - Privacy Policy