How to Export Data from MySQL to a CSV File using PHP

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

Process Logic

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

Code Example for Direct Download

This script will immediately prompt the browser to download a file named export.csv when executed.

<?php
$streznik = "localhost";
$uporabnik = "root";
$geslo = "";
$baza_podatkov = "your_database_name";

// Create connection
$povezava = new mysqli($streznik, $uporabnik, $geslo, $baza_podatkov);

// Check connection
if ($povezava->connect_error) {
    die("Connection failed: " . $povezava->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, ime, povezava FROM imena ORDER BY id ASC";
$result = $povezava->query($sql);

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

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