What are some common pitfalls when exporting data from a MySQL database to a CSV file using PHP?

One common pitfall when exporting data from a MySQL database to a CSV file using PHP is not properly handling special characters or newlines within the data. To solve this issue, it is important to properly escape the data before writing it to the CSV file. 

// Connect to MySQL database
$mysqli = new mysqli("localhost", "username", "password", "database");

// Check connection
if ($mysqli->connect_error) {
    die("Connection failed: " . $mysqli->connect_error);
}

// Select data from MySQL table
$query = "SELECT * FROM table";
$result = $mysqli->query($query);

// Create CSV file and write data
$fp = fopen('export.csv', 'w');

// Write column headers
fputcsv($fp, array('Column1', 'Column2', 'Column3'));

// Write data rows
while ($row = $result->fetch_assoc()) {
    // Escape special characters before writing to CSV
    foreach ($row as $key => $value) {
        $row[$key] = str_replace('"', '""', $value);
    }
    fputcsv($fp, $row);
}

// Close file and database connection
fclose($fp);
$mysqli->close();

Keywords

MySQL CSV PHP data export error handling

Related Questions