CODEWITHSUNDEEP
phpmysqlexport
Kirk Logan
Kirk Logan

Reputation: 783

File Export Script Hangs on Large Output

Below is the script im using to export a MySQL database to a .csv file.

It seems to do fine with smaller outputs (under about 30,000 rows), but hangs on my larger outputs and just wont complete (1 million rows total).

Any ideas why?

Also, the PHPMyAdmin export utility dumps it incredibly quickly (just a few seconds). Wondering how it does it.

if(isset($_POST['submit'])) {
// Fetch Record from Database
$output = "";
$table = "tt_output";
$sql = mysqli_query($connection,"select * from {$table}");
$columns_total = mysqli_num_fields($sql);

// Get The Field Name
$query = "SHOW COLUMNS FROM {$table}"; $result_set = mysqli_query($connection,$query);
while ($result = mysqli_fetch_array($result_set)) {
    $heading = $result[0];
    $output .= trim($heading.',');
}

$output = substr($output,0,strlen($output)-1)."\r\n";

// Get Records from the table
while ($row = mysqli_fetch_array($sql)) {
    for ($i = 0; $i < $columns_total; $i++) {
        $output .='"'.$row["$i"].'",';
    }
    $output = substr($output,0,strlen($output)-1)."\r\n";
}

// Download the file
$filename = "output".".csv";
header("Pragma: public", true);
header("Expires: 0"); // set expiration time
header("Cache-Control: must-revalidate, post-check=0, pre-check=0");
header("Content-Type: application/force-download");
header("Content-Type: application/octet-stream");
header("Content-Type: application/download");
header('Content-Disposition: attachment; filename='.$filename);
header("Content-Transfer-Encoding: binary");
header('Connection: Keep-Alive');

echo $output;
exit;

}

Upvotes: 1

Views: 800

Answers (1)

jeroen
jeroen

Reputation: 91792

On problem I can see is that you are reading everything into a variable / memory and only then start outputting it.

You should put your loop below the headers and output it right away:

...
header('Connection: Keep-Alive');

// Get Records from the table
while ($row = mysqli_fetch_array($sql)) {
    for ($i = 0; $i < $columns_total; $i++) {
        $output .='"'.$row["$i"].'",';
    }
    echo substr($output,0,strlen($output)-1)."\r\n";
}

Another possible problem is how you are building your row. If any of the fields contains an unescaped " character, it will break your csv. You might want to use fputcsv() to save the csv to a temporary file and only then send it to the client.

You can also save yourself a query as the column names will be the keys of your results arrays and if you really want to build the csv manually, you could use implode() instead of a loop but you would have to test if that is faster.

Upvotes: 1

Related Questions