What are some best practices for handling and formatting data when exporting it from MySQL to .csv using PHP?

When exporting data from MySQL to .csv using PHP, it is important to properly handle and format the data to ensure it is correctly displayed in the .csv file. One best practice is to use fputcsv() function to format each row of data before writing it to the .csv file. Additionally, it is recommended to set appropriate headers for the .csv file to specify the file type and enable proper downloading. 

<?php
// Connect to MySQL database
$connection = mysqli_connect("localhost", "username", "password", "database");

// Query to fetch data from MySQL
$query = "SELECT * FROM table";
$result = mysqli_query($connection, $query);

// Create and open .csv file for writing
$filename = "data.csv";
$file = fopen($filename, "w");

// Write headers to .csv file
fputcsv($file, array("Column 1", "Column 2", "Column 3"));

// Loop through MySQL results and write data to .csv file
while ($row = mysqli_fetch_assoc($result)) {
    fputcsv($file, $row);
}

// Close .csv file
fclose($file);

// Set headers for .csv file
header('Content-Type: text/csv');
header('Content-Disposition: attachment; filename="' . $filename . '"');

// Output .csv file
readfile($filename);

// Close MySQL connection
mysqli_close($connection);
?>

Keywords

MySQL CSV PHP data formatting export

Related Questions