shanal
shanal

Reputation: 103

Export large data to CSV using PHP

I am trying to export some data to CSV using PHP.

My code:

function download_send_headers($filename) {
    // disable caching
    $now = gmdate("D, d M Y H:i:s");
    header("Expires: Tue, 03 Jul 2001 06:00:00 GMT");
    header("Cache-Control: max-age=0, no-cache, must-revalidate, proxy-revalidate");
    header("Last-Modified: {$now} GMT");

    // force download  
    header("Content-Type: application/force-download");
    header("Content-Type: application/octet-stream");
    header("Content-Type: application/download");

    // disposition / encoding on response body
    header("Content-Disposition: attachment;filename={$filename}");
    header("Content-Transfer-Encoding: binary");
}

$givenTerm = $_POST['TextBoxList'];

$sql_query = "select * from some_table where key=".$givenTerm;
$result = sqlsrv_query($conn,$sql_query);
$r1 = array();
$file = fopen("php://output", 'w');
while ($row = sqlsrv_fetch_array($result, SQLSRV_FETCH_NUMERIC)){
    $valuesArray=array();
    foreach($row as $name => $value)
    {
        $valuesArray[]=$value;
    }
    fputcsv($file,$valuesArray);
}
fclose($file);
download_send_headers("data_export_" . date("Y-m-d") . ".csv");

die();

Here I am receiving a search-value from a form in another page and then triggering a query based on it. The code is working fine for smaller data-sets(up to 100). But when the number of rows is more in the result (around 600-700) then the code crashes. Please tell me how to fix this.

P.S. I am using Sql Server 2012 for the database.

Upvotes: 0

Views: 4152

Answers (1)

GolezTrol
GolezTrol

Reputation: 116200

Instead of first getting all results in a single array and then outputting it, you could also fetch it row by row and outputting each row directly. That way you won't need the massive amount of memory to fit the entire array in.

Upvotes: 1

Related Questions