How Can I Generate and Download CSV Files from a MySQL Database Using PHP?

Generating CSV Files for Users in PHP

Users often request data extracts in CSV format. This article delves into the process of creating CSV files on demand and delivering them to users through a downloadable link.

Solution:

To create and download a CSV file containing data from a MySQL database when a user clicks a link:

1. Generate the CSV Data:

   • Perform the MySQL query to retrieve the desired data.
   • Organize the data into an array of arrays with each row representing a record.

2. Create the CSV Header:

   • Use the header() function to set the content type to text/csv and specify the desired filename as a content disposition. This parameter specifies the filename and triggers the download prompt.

3. Define a CSV Output Function:

   • Create a function named outputCSV() that accepts an array of records as an argument.
   • Within the function, open a stream to write to the output using fopen("php://output", "wb").
   • Iterate over the array of records and write each row to the output stream using fputcsv($output, $row). This function automatically handles delimiters and enclosures.
   • Close the output stream using fclose($output).

4. Call the CSV Output Function:

   • Pass the array of records to the outputCSV() function.
   • The CSV file will be automatically generated and sent to the client for download.

Example Code:

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

function outputCSV($data) {
  $output = fopen("php://output", "wb");
  foreach ($data as $row)
    fputcsv($output, $row); // here you can change delimiter/enclosure
  fclose($output);
}

outputCSV(array(
  array("name 1", "age 1", "city 1"),
  array("name 2", "age 2", "city 2"),
  array("name 3", "age 3", "city 3")
));

Key Points:

• The php://output pseudo-variable opens a stream that is automatically written to the HTTP output buffer.
• fputcsv() writes a line of CSV data to the stream, handling delimiter and enclosure settings.

The above is the detailed content of How Can I Generate and Download CSV Files from a MySQL Database Using PHP?. For more information, please follow other related articles on the PHP Chinese website!