load big file into database by php

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
orlandinho
Forum Newbie
Posts: 18
Joined: Mon Feb 12, 2007 9:50 pm

load big file into database by php

Post by orlandinho »

hi

i am doing an app that needs maintenance every month
this maintenance is the creation of a table for the month and the upload of a file with nearly 20 million rows to populate the table

this should be done by a web interface

the problem here is the time it will take to complete this process, there may be a php max execution time error

i was thinking about using async call to leave this process running

maybe someone can help with any solution

PD: i am working with mysql 5.0.27

thanks
User avatar
s.dot
Tranquility In Moderation
Posts: 5001
Joined: Sun Feb 06, 2005 7:18 pm
Location: Indiana

Post by s.dot »

The following will make sure the php script won't time out

Code: Select all

set_time_limit(0);
ignore_user_abort(true); //just in case someone closes the browser
For the uploading of the file, is it an SQL file?

You could use a form to upload the file to the server, then something like the following to execute it

Code: Select all

exec('mysql -u username -h localhost -p');
exec('use database_name');
exec('\. /path/to/'.$uploaded_file');
I'm not sure how well that would work. But that's my input. ;)
Set Search Time - A google chrome extension. When you search only results from the past year (or set time period) are displayed. Helps tremendously when using new technologies to avoid outdated results.
User avatar
pickle
Briney Mod
Posts: 6445
Joined: Mon Jan 19, 2004 6:11 pm
Location: 53.01N x 112.48W
Contact:

Post by pickle »

Is this an SQL file? Could you use phpMyAdmin to import it?

I know any time a PHP script spends waiting around for the database to finish does NOT count towards the 30 second time out (so you can be waiting for hours for MySQL to finish & your script won't time out).
Real programmers don't comment their code. If it was hard to write, it should be hard to understand.
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

User raghavan20 has a bunch of threads involving the decomposition of an SQL file into individual statements that can be processed. If memory serves, the meat is in the Regex board.

There's also tools like mysqlimport and bigdump
orlandinho
Forum Newbie
Posts: 18
Joined: Mon Feb 12, 2007 9:50 pm

Post by orlandinho »

actually the file is a csv file with 20 million rows aprox
when i load this data by mysql prompt it takes nearly 4 hours
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

fgetcsv() may be of interest.

A script will take even longer than the command line.
Post Reply