PHP MySQL import large CSV file...

Hello,

I need to import a large amount of data into MySQL from a CSV file.  The data in question is a group of 50,000 or more line items, which need to be written to various tables in my database for proper normalization.

I have class strucutures in the form of parents that house collections of line items that are designed well and offer what I need when the user is editing a few of the items, or wants to otherwise manuipulate the data structure(s).

The problem is, that during an import process, the performance is quite piss-poor, if I use my collections in php to write to the database:
i.e.

foreach ($oProject->lineItemCollection as $oLineItem)
   $oLineItem->writeDB();

Without stored procedures yet in MySQL, is there a 'proper' way I could compile an .SQL script in PHP, and then submit it to the MySQL server, which would then execute the 12,000 queries on ONE database access, rather than looping in php 10,000 times with
mysql_query($query[$i]);

Thanks,
Scott


LVL 1
kbachAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

alskdj80Commented:
hmm, ive never tried this before, so use at your own risk, but it seems it would solve your problem... what you could do is make a file... dump in all your 50000 INSERT statements... name it something.sql

then use php shell_exec() to run command: mysql databaseName -uuser -ppassword < something.sql
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Bernard S.CTOCommented:
Alskdj80 is pointing you to a good direction: avoid using a php "front" to do that, consider importing DIRECTLY the csvb file, either thru phpmyadmin (should fail miserrably 'cause of CPU time) or into mysql itself. For a similar problem but with quite smaller file (although they were in UTF-8, which "compensates"), direct load i,nto mysql was the only option.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.