Export large data to csv with php and ajax

Question

I have a problem with exporting large amount of data to csv file using php.

Information:

Need to export 700000 list of addresses from database table(address).
Server timed out or lacking memory
project I'm working on working with multiple servers

My solution(what have i tried)

Get data part by part(from database) process this data(fputcsv) write this part to the temporary file - and send information to user via Ajax (show him the amount of processed Percentage). After last part of data has been processed just give user link to download this file. All is fine i have did this and this solution works for me - on my local enviroment, but

the problem is - project I'm working on working with multiple servers so I ran into a problem that temporary file can be stored on different servers.

For Example:

I have 3 servers: Server1, Server2 and Server3.

First time i read data from db with limit 0 50000 - process it and save it to File.csv on Server1, next iteration limit 50000, 50000 can be saved on another server Server2 - this is the problem.

So my question is:

Where i can store my processed temporary csv data, or maybe i am missing something, i am stuck here, looking for advice. Every suggestion or solution will be appreciated! Thanks.

UPDATE

PROBLEM IS SOLVED

Later i will post my solution

not clear on the multiple servers, do you mean multiple db servers containing the data? — , Jun 18 '14 at 20:02
Can you use/setup a job queue system where the actual work is done by a background task on the server instead of a http request ? maybe beanstalkd - http://www.lornajane.net/posts/2014/working-with-php-and-beanstalkd — Amine Matmati, Jun 18 '14 at 20:07
@Amine Matmati, i think so, but I do not understand a bit about what exactly is it — Petro Popelyshko, Jun 18 '14 at 20:10
I hope this tutotial series helps - http://kamisama.me/2012/10/09/background-jobs-with-php-and-resque-part-1-introduction/ — Amine Matmati, Jun 18 '14 at 20:11
@Dagon, i am working with team on a big project highload, and we are using a lot of servers — Petro Popelyshko, Jun 18 '14 at 20:24

score 1 · Answer 1 · edited Jun 26 '14 at 12:27

1

You can use the mysql query with limits, to dircly export the records into csv file from mysql database.

SELECT id, name, email INTO OUTFILE '/tmp/result.csv'
FIELDS TERMINATED BY ',' OPTIONALLY ENCLOSED BY '"'
ESCAPED BY ‘\\’
LINES TERMINATED BY '\n'
FROM users WHERE 1

edited Jun 26 '14 at 12:27

svvac

4,756
3
15
22

answered Jun 26 '14 at 12:08

Parvind

231
2
3

score 0 · Answer 2 · edited May 23 '17 at 12:09

It would really be helpful if you posted your code. The reason I'm saying that is because it doesn't sound like you're looping row after row which is will save you heaps of memory - no huge array to keep in the RAM. If you're not looping row by row and committing to the CSV file as you go, then I suggest you modify your code to do just that and it might solve the issue altogether.

If indeed, even committing to the CSV row by row is not enough. Then the issue you're running into is your servers setup relies on the code to be stateless, but your code isn't.

You can solve this issue using either of the following ways:

Make user sessions server specific. If you're routing requests via a load balancer, then you can probably control that setting there. If not, you'll have to go into custom sessions variables and configure your environment accordingly. I don't like this method but if you can control it via the load balancer, it might be the quickest way to get the problem solved.
Save the temporary files to the shared DB all the servers have access to with a simple transaction ID or some other identifier. Make the server handling this last portion of the export aggregate the data and prepare the file for download.
Potentially, you could run into another memory limit or max run time issue with method #2. In this case, if you cannot raise the servers' RAM, configure PHP to use the extra RAM and extend the script max run time. Then my suggestion would be to let the user download the files portion by portion. Export the CSV up to the limit your server supports, let the user download, then let them download the next file, and so on.
Potentially, you should try this method before you try any of the other methods. But perhaps the question which we must be asking is why use PHP to convert database entries into CSV in the first place? A lot of DBs have a CSV export built-in which is almost guaranteed to take less memory and time. If you're using MySQL for example, you can use - How to output MySQL query results in CSV format?

Hope this helps.

score 0 · Answer 3 · answered Jun 26 '14 at 15:55

0

you can increase the execution time of your php code using ini_set('max_execution_time', seconds in numbers);

answered Jun 26 '14 at 15:55

ameenulla0007

2,655
1
10
15

Export large data to csv with php and ajax

3 Answers3