Exporting a large CSV via PHP results in timeout/memory allocate error

Question

I have a tool that allows data to be exported based on different time ranges. The problem I run into is that the longer the time range, the more data, and eventually the data set is too large -- resulting in either a timeout error or a memory allocation error.

Short of changing php.ini to have a larger max_execution_time, etc, is there a better way of doing this?

Here's my script to build the CSV (the user selects export, then this page is loaded):

$fileName           = "export-".date("YmdHis");
$exportSignature    = array(" ","Export","Generated by: ".$_SESSION['name'],date("Y-m-d H:i:s"));

header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename='.$fileName.'.csv');

// create a file pointer connected to the output stream
$output = fopen('php://output', 'w');

// run all the queries for the data
if($exportType == "list") {
    include('include/query-compare.php');   
} elseif($exportType == "analyze") {
    include('include/query-analyze.php');   
} elseif($exportType == "funnel") {
    include('include/query-funnel.php');    
} elseif($exportType == "funnelTrend") {
    include('include/query-funnel-trends.php'); 
}

// add column headers
fputcsv($output,$columnHeaders);

// add row data
if($exportType == "list") {
    foreach($comparison as $account) {
        fputcsv($output,$account);
    }
} elseif($exportType == "analyze") {
    foreach($analyze as $account) {
        fputcsv($output,$account);  
    }
} elseif($exportType == "funnel" || $exportType == "funnelTrend") {
    foreach($funnel as $line) {
        fputcsv($output,$line); 
    }
}   

// add export signature
foreach($exportSignature as $line) {
    fputcsv($output,array($line));
}

Here's a sample of the array that's used to create the rows. This is the variable -- if the time range is small there might be a few hundred entries in the array, if it's large, there's tens of thousands.

Array
(
    [0] => Array
        (
            [0] => 1
            [firstName] => Marco
            [lastName] => R.
            [title] => D
            [email] => test@test.com
            [company] => xx
            [lSource] => xx
            [lDetail] => xx
            [lExact] => xx
            [createdAt] => 2017-06-26 00:00:00
            [nDate] => 2017-08-15
            [cDate] => 2017-08-15
            [cMoment] => xx
            [mDate] => 2017-08-15
            [mMoment] => xx
            [Id] => 003Axx
            [Type] => Contact
            [accountId] => 001A0xx
            [parentAccountId] => 
            [accountOwner] => Kevin S.
            [xx] => Nicholas W.
            [accountType] => Prospect
            [totalARR] => 0.00
            [accountTier] => 
            [ty] => XX
            [industry] => Corporate Services
            [secondaryIndustry] => IT Services and Consulting
            [billingCountry] => Germany
            [1] => 006Axx
            [2] => PS (New Business)
            [3] => Kevin S.
            [4] => Nicholas W.
            [5] => cc
            [6] => New Business
            [7] => Identify
            [8] => 40000.00
            [9] => 2017-08-16
            [10] => 2018-07-27
            [11] => 2017-08-16
            [12] => 2017-08-21
        )
)

I'd be fine with scheduling the export and emailing it to the user as well, I'm just not familiar with how I can have this large CSV constructed in the background without timing out.

EDIT: would it be a better option to queue the export in a database and have a cron job that looks for the exports, then runs the export in the background until complete? Since it's running via the cron it shouldn't have a timeout, right?

Answer 1

Using set_time_limit ( 0 ); didn't help since I'm running on nginx and would run into gateway errors. Instead, I wound up queuing the downloads into a database table and executing the actual queries via a cron job, as those aren't subject to the same timeout limitations.

Answer 2

Use set_time_limit ( 0 ); at the beginning of your file. For example:

set_time_limit ( 0 );
$fileName           = "export-".date("YmdHis");