Exporting MySQL Data to Excel/CSV Using PHP: An Advanced Guide

Exporting data from a MySQL database to Excel or CSV files is a common requirement in web applications. Whether you're building an admin panel, data reporting dashboard, or automating a backup process, efficient data export is a must-have feature.

In this blog, we’ll walk through advanced techniques for exporting MySQL data to CSV and Excel formats using PHP, with best practices for performance, security, and file handling.

Tools & Libraries We'll Use

• PHP (>=7.4 or PHP 8+)
• MySQLi or PDO
• PhpSpreadsheet for Excel (.xlsx) export
• Native PHP functions for CSV export

Overview

1. Connect to MySQL Database
2. Fetch Data Securely
3. Export to CSV (Native PHP)
4. Export to Excel (.xlsx using PhpSpreadsheet)
5. Download the File via Browser
6. Performance & Security Tips

1. Connecting to the Database (Using PDO)

<?php
$dsn = 'mysql:host=localhost;dbname=your_database;charset=utf8mb4';
$username = 'your_user';
$password = 'your_password';

try {
    $pdo = new PDO($dsn, $username, $password, [
        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
    ]);
} catch (PDOException $e) {
    die("Connection failed: " . $e->getMessage());
}
?>

2. Fetching Data Securely

$query = "SELECT id, name, email, created_at FROM users";
$stmt = $pdo->prepare($query);
$stmt->execute();
$data = $stmt->fetchAll(PDO::FETCH_ASSOC);

3. Export to CSV (Native PHP)

<?php
$filename = "users_export_" . date("Y-m-d_H-i-s") . ".csv";

header('Content-Type: text/csv');
header("Content-Disposition: attachment; filename=\"$filename\"");

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

// Output header row
fputcsv($output, array_keys($data[0]));

// Output data rows
foreach ($data as $row) {
    fputcsv($output, $row);
}

fclose($output);
exit;
?>

Pros

• Very fast for large datasets
• Minimal memory usage
• No external dependencies

Cons

• No formatting or styling
• Limited to basic text data

4. Export to Excel (.xlsx using PhpSpreadsheet)

Install PhpSpreadsheet via Composer

composer require phpoffice/phpspreadsheet

Export Logic

<?php
require 'vendor/autoload.php';

use PhpOffice\PhpSpreadsheet\Spreadsheet;
use PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet();

// Header
$sheet->fromArray(array_keys($data[0]), null, 'A1');

// Data rows
$sheet->fromArray($data, null, 'A2');

// File output
$filename = "users_export_" . date("Y-m-d_H-i-s") . ".xlsx";
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header("Content-Disposition: attachment; filename=\"$filename\"");

$writer = new Xlsx($spreadsheet);
$writer->save('php://output');
exit;
?>

Pros

• Professional Excel formatting
• Cell styling, formulas, charts supported
• Supports CSV, XLSX, ODS, PDF

Cons

• Heavier memory usage
• Slower for large datasets

5. Prompting a File Download

if ($_GET['format'] === 'csv') {
    exportToCSV($data);
} elseif ($_GET['format'] === 'excel') {
    exportToExcel($data);
} else {
    echo "Invalid format selected.";
}

6. Performance & Security Best Practices

Security

• Sanitize and validate user input
• Always use prepared statements
• Do not expose internal file paths or database structure

Performance

• For large exports, stream results row-by-row
• Use yield or iterators for memory efficiency
• Set set_time_limit(0) for long-running processes

Advanced: Streaming Millions of Rows (CSV)

$stmt = $pdo->prepare('SELECT id, name FROM huge_table');
$stmt->execute();

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

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

$isFirst = true;
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {
    if ($isFirst) {
        fputcsv($output, array_keys($row));
        $isFirst = false;
    }
    fputcsv($output, $row);
}

fclose($output);
exit;

Conclusion

Exporting MySQL data to Excel or CSV using PHP can be quick and robust with the right tools. For simple data dumps, CSV is efficient and sufficient. For well-formatted reports, use PhpSpreadsheet to create professional Excel files.

Whether you’re building a dashboard or automating business reports, exporting data reliably is a key step in making your app more powerful and user-friendly.

👉 Download mysql_export_demo.zip