Data importing practices

dustsmoke

I was doing some things today that involved me pulling data from a csv file and tossing it into mysql. It got me thinking as I am new to php.

Would I not want to somehow get this in one query statement instead of executing a whole bunch of seperate inserts by looping through the array that I made from the csv file.

I know MySQL can do;

INSERT INTO table (bla, bla) VALUES
('a','b'),
('c','d');

Instead of just sql standard;
INSERT INTO table (bla, bla) VALUES ('a','b');
INSERT INTO table (bla, bla) VALUES ('c','d');

So my question is..... Is there a better way to do this other than what I have already said? Also, is there a good way to execute a script of commands?

AstroTeg

Depends on what you want to do now and in the future.

I personally stay clear of this:
INSERT INTO table (bla, bla) VALUES
('a','b'),
('c','d');

I believe this SQL insert format is mostly MySQL (meaning, if you wish to take this insert to another DB, you'd have to rewrite it).

But if you're jiggy with it, then go for it.

The supper fast approach is to actually build a CSV file and import it on the command line. MySQL loves this and can process the file in seconds versus minutes through PHP and insert statements.

Roger_Ramjet

Mysql has the statement load data infile which is very fast and efficient for this. It is what phpmyadmin uses.

dustsmoke

Thanks for the replys, I will probably use the load_data_infile