How Can I Export Database Data to a CSV File Using PHP?

Exporting Database Data to CSV with PHP

In the realm of data management, the need may arise to extract information from a database and export it in a convenient format. If your preferred format is CSV (comma-separated values), PHP provides a robust solution for this task.

Generating CSV Content

To create CSV content from an array, you can utilize the following function:

function array2csv(array &$array)
{
   if (count($array) == 0) {
     return null;
   }
   ob_start();
   $df = fopen("php://output", 'w');
   fputcsv($df, array_keys(reset($array)));
   foreach ($array as $row) {
      fputcsv($df, $row);
   }
   fclose($df);
   return ob_get_clean();
}

This function first checks if the array is empty. If it's not, it starts buffering the output, opens a handle to the output stream, writes the header row containing the keys from the first array element, and then iterates over the array, writing each row to the output stream using fputcsv(). Finally, it closes the output handle and returns the buffered CSV content.

Initiating Download

Once you have the CSV content, you can enable the user to download the file using the following function:

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");
}

In this function, the caching mechanisms are disabled, and the file is forced to download by setting appropriate headers. The Content-Disposition header specifies the filename and sets the disposition as an attachment, while the Content-Transfer-Encoding header indicates binary transfer.

Usage Example

To show how to use these functions, consider the following example:

download_send_headers("data_export_" . date("Y-m-d") . ".csv");
echo array2csv($array);
die();

This example sets the download headers with the specified filename, outputs the CSV content, and then terminates the execution to avoid any additional output that could corrupt the file.

The above is the detailed content of How Can I Export Database Data to a CSV File Using PHP?. For more information, please follow other related articles on the PHP Chinese website!