Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

PHP MySQL import large CSV file...

Posted on 2004-11-01
2
Medium Priority
?
2,173 Views
Last Modified: 2008-01-09
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


0
Comment
Question by:kbach
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 3

Accepted Solution

by:
alskdj80 earned 300 total points
ID: 12465563
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
 
LVL 29

Expert Comment

by:fibo
ID: 12537805
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

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

These days socially coordinated efforts have turned into a critical requirement for enterprises.
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …
Suggested Courses

604 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question