CODEWITHSUNDEEP
phpapachecsvmysqli
Andreas
Andreas

Reputation: 553

csv export with php stops when huge amounts of data shall be exported

I am using a php script to perform a csv export.

When exporting small amounts of data it works fine, like 10k to 100k records.

However when more data shall get exported, the export stops at some point and the csv file is incomplete.

For example, when exporting 500k records, it will only export around 300k...

It is interesting that the stop appears not always at the same point - sometimes the exported file has 23mb sometimes 26mb sometimes 24mb and so on...

I guess the porblem is to be found somewhere in the php.ini, like a memory or a cache setting that is to low.

However I am not an expert in setting php - any ideas?

Here is the code that I use to perform the csv export:

mysqli_select_db($conn, "$settings_incident_database");
$sql = "SELECT *
        FROM $settings_incident_database.incidents
        $where
        ORDER BY $settings_incident_database.incidents.Id DESC";

$result = mysqli_query($conn, $sql);

header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=export.csv');

$row = mysqli_fetch_assoc($result);
if ($row) {
    echocsv(array_keys($row));
}

while ($row) {
    echocsv($row);
    $row = mysqli_fetch_assoc($result);
}

function echocsv($fields)
{
    $separator = '';
    foreach ($fields as $field) {
        if (preg_match('/\\r|\\n|,|"/', $field)) {
            $field = '"' . str_replace('"', '""', $field) . '"';
        }
        echo $separator . $field;
        $separator = ';';
    }
    echo "\r\n";
}

Upvotes: 0

Views: 2081

Answers (3)

Mark Baker
Mark Baker

Reputation: 212452

Changing your code to use PHP's built-in fputcsv() function is as simple as changing 

if ($row) {
    echocsv(array_keys($row));
}

while ($row) {
    echocsv($row);
    $row = mysqli_fetch_assoc($result);
}

to

$csv = fopen('php://output', 'w');
if ($row) {
    fputcsv($csv, array_keys($row));
}

while ($row) {
    fputcsv($csv, $row);
    $row = mysqli_fetch_assoc($result);
}

and then you can delete your echocsv function completely

Upvotes: 0

Andreas
Andreas

Reputation: 553

I have found the problem in the php.ini

max_execution_time = 30 -> changed to 300
memory_limit = 128M -> changed to 512M

Chganging these settings solved the problem for me.

I will also consider the PHP's built-in fputcsv() function to make the code somehow less badly, but for a quick fix this is enough.

Thx.

Upvotes: 0

Samuil Banti
Samuil Banti

Reputation: 1795

I assume that you're using too much memory. You may try to export the content in temporary file using MySQL like this:

$tmp_csv_file = '/tmp/test.csv';

mysqli_select_db($conn, "$settings_incident_database");
$sql = "SELECT *
        FROM $settings_incident_database.incidents
        $where
        ORDER BY $settings_incident_database.incidents.Id DESC
    INTO OUTFILE ".$tmp_csv_file."
    FIELDS TERMINATED BY ','
    ENCLOSED BY '\"'
    LINES TERMINATED BY '\n'
"; // Get temporary CSV file via MySQL

$result = mysqli_query($conn, $sql);

$csv_content = file_get_contents($tmp_csv_file); // Get the CSV content
unlink($tmp_csv_file); // Delete the temporary file

header('Content-Type: text/csv');
header('Content-Disposition: attachment;filename=export.csv');
echo $csv_content;

NOTE: Keep in mind that the temporary file must be unique if you expect multiple users.

NOTE2: If you use readfile() as Mark suggests it may be more efficient, but you'll need to delete the temporary file after the output.

Upvotes: 2

Related Questions