Export Data in CSV Using PHP

In this article, we are going to download/export the data in a CSV file from the database using PHP. We are going to use the fopen(), fputcsv() to create the CSV file.

Check Output Buffer

First of all, turn off the output buffer, if one exists by adding the ob_end_flush() or ob_end_clean() function at the start of your PHP file.

Because if you have set any value other than Off to output_buffering, PHP will automatically create an output buffer that stores all the output that your script tries to send through the response body.

if (ob_get_level()) {
    ob_end_clean();
}

Add headers for CSV file

The next step to add headers that help to make our output a CSV attachment. In the case of dynamic content, we must stop caching the output to the client’s browser by adding the Pragma and Expires headers.

$csv = 'csv_filename.csv';
header( "Content-Type: text/csv;charset=utf-8" );
header( "Content-Disposition: attachment;filename=\"$csv\"" );
header( "Pragma: no-cache" );
header( "Expires: 0" );

Collect Data from MySQL

The next step is to collect the data from the database. It’s up to you and the application’s structure to collect the data from the database. Many people’s preferred classes, functions, or direct queries from the database.

We are using a separate function to return data in the form of array

$array = get_your_csv_data_from_database();

Make CSV file with PHP

The next step to making the CSV file with the accessed data from the database. We are going to use the fputcsv() function which helps us to add data format as CSV.

$fp = fopen('php://output', 'w');
fputcsv($fp, array_keys($array), ';', '"');

foreach ($array as $i => $fields) {
    fputcsv($fp, $fields, ';', '"');
    if ($i % 100 == 0) {
        flush();
    }
}

fclose($fp);

The php://output is a write-only stream that allows you to write to the output buffer mechanism.

The first fputcsv() function is used to make the titles to the CSV file by taking the keys of an array data and next collect all the data one by one by using the foreach() function.

fputcsv($fp, array_keys($array), ';', '"');

The flush() function helps to flush the output to the browser after 100 lines of CSV data.

Complete Code

if (ob_get_level()) {
    ob_end_clean();
}

$csv = 'csv_filename.csv';
header( "Content-Type: text/csv;charset=utf-8" );
header( "Content-Disposition: attachment;filename=\"$csv\"" );
header( "Pragma: no-cache" );
header( "Expires: 0" );

// Get the headers out immediately to show the download dialog
flush(); 

$array = get_your_csv_data_from_database(); 

$fp = fopen('php://output', 'w');
fputcsv($fp, array_keys($array), ';', '"');

foreach ($array as $i => $fields) {
    fputcsv($fp, $fields, ';', '"');
    if ($i % 100 == 0) {
        flush();
    }
}

fclose($fp);

We hope this article will help you to learn the how to download data in CSV with PHP. If you like it then please follow us on Facebook and Twitter.