How to Export Data from MySQL Database to CSV File with PHP

In the previous post, we looked at how to import data; now we'll do the reverse. Exporting data from MySQL to CSV is extremely useful for creating backups, reports, or transferring data between different applications.

Process Logic

• Query: First, we retrieve the desired data from the table using a SELECT statement.
• Headers: We must send appropriate headers to the browser so it recognizes the file as CSV and offers it for download.
• Writing: We write data from the database directly to the output stream using the fputcsv() function.

Example Code for Direct Download

This script will immediately offer the file export.csv for download when run in the browser.

<?php
$server = "localhost";
$username = "root";
$password = "";
$database = "your_database_name";

// Create connection
$connection = new mysqli($server, $username, $password, $database);

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

// Set filename and download headers
$filename = "data_export_" . date('Y-m-d') . ".csv";
header('Content-Type: text/csv; charset=utf-8');
header('Content-Disposition: attachment; filename=' . $filename);

// Open output stream
$output = fopen('php://output', 'w');

// Write column headers
fputcsv($output, ['ID', 'Name', 'Link']);

// Retrieve data from database
$sql = "SELECT id, name, link FROM names ORDER BY id ASC";
$result = $connection->query($sql);

if ($result->num_rows > 0) {
    while ($row = $result->fetch_assoc()) {
        fputcsv($output, $row);
    }
}

fclose($output);
$connection->close();
exit();
?>