Insert Into (single row vs multiple rows)

I am using a LAMP Script 'Cardfile' the table Structure is shown below. My server is runing php 4.3.3 & MysQL 4.0.16 I am using phpmyadmin to run the querry,

The query below works fine for inserting a single row/record but I want to insert a couple hundred. I have three books on Php/Mysql and it seems they all show how to insert a single row/record but not multiples.

What modifications to this query do I need to make, to addmultiple records. If there is a better (easier) wasy to do this I'd appreciate any tips.

INSERT INTO cardfile (display_name,first,last,address,city,state,zip,ph one,fax,cell,pager,email,website,company,category, comments,created)

VALUES ('Homer Simpson','Homer','Simpson','123 Springfield Dr','Springfield','IN','12345','1-123-456-7890','1-123-456-7890',

'1-123-456-7890','1-123-456-7890','homer@atomicplant.com','thesimpsons.com','a tomic plant inc','customer','I love this guy!',now());

Here is (TABLE cardfile) Structure

id integer(11) not null auto_increment,

userid integer(11) default 0 not null,

display_name varchar(50) null,

first varchar(40) null,

last varchar(40) null,

address varchar(100) null,

city varchar(40) null,

state char(2) null,

zip varchar(20) null,

phone varchar(20) null,

fax varchar(20) null,

cell varchar(20) null,

pager varchar(20) null,

email varchar(50) null,

website varchar(50) null,

company varchar(50) null,

category varchar(40) null,

comments text null,

modified timestamp(14) null,

created timestamp(14) null,

PRIMARY KEY (id) );

To add multiple records in a single query, add multiple value groups in the query. I.e., follow the following format:

INSERT INTO [table]
( [field1], [field2], [field3] ) VALUES
( '[value1.1]', '[value1.2]', '[value1.3]' ),
( '[value2.1]', '[value2.2]', '[value2.3]' ),
( '[value3.1]', '[value3.2]', '[value3.3]' ),
etc.

Here's an example of adding two rows, expanding upon your example:

INSERT INTO cardfile (display_name, first, last, address, city, state, zip, phone, fax, cell, pager, email, website, company, category, comments, created) VALUES ('Homer Simpson', 'Homer', 'Simpson', '123 Springfield Dr', 'Springfield', 'IN', '12345', '1-123-456-7890', '1-123-456-7890', '1-123-456-7890', '1-123-456-7890', 'homer@atomicplant.com', 'thesimpsons.com', 'atomic plant inc', 'customer', 'I love this guy!', now()), ('Eric Cartman', 'Eric', 'Cartman', '987 S. Park Ave.', 'South Park', 'CO', '98765', '1-555-555-5555', '1-555-555-5555', '1-555-555-5555', '1-555-555-5555', 'ecartman@ihateyouguys.com', 'http://www.southpark.com/', 'South Park Elementary', 'customer', 'Respect my authoritah', now());


If you have your data in a normalized, delimited file or in a spreadsheet, you should be able to loop through the data using PHP without having to copy-and-paste or retype it -- just parse the data and write it to the database. It will save you lots of time and significantly shorten your code. You can either perform a database insert for each loop, or concatenate the value groups and perform a single database insert after the loop has ended.

Good luck.

In SQL, after creation of table we insert data using INSERT keyword. Suppose we have to insert 10 records, we write 10 INSERT statement for inserting them. However, in SQL Server 20008 a new feature called Row constructor has been introduced which allows us to write a single INSERT statement for inserting more than one record at a time.

Hope it did answer the question