Export MySQL data to Excel in PHP

Export MySQL data to Excel in PHP

  1. Blog
  2. 15 November 2026
  3. 4 min read

Stefan Bogdanescu

Founder & Senior Architect

Export MySQL Data to Excel in PHP: Moving Beyond Tab-Delimited Text

Exporting data from a database to an Excel file is a common requirement, but the approach you choose dictates whether your users get a usable spreadsheet or a broken text file that opens into one giant column. The code snippet provided attempts to solve this by generating CSV-like output and forcing the browser to download it as .xls. While this mimics Excel's format, it relies on Excel's legacy ability to interpret tabs and quotes, which often leads to exactly the problem described: all data collapsing into a single cell or formatting breaking when text contains delimiters.

The core issue in the provided implementation is the assumption that simply printing tab-separated values with escaped quotes will render correctly across different environments. Modern versions of Excel and Google Sheets handle CSVs (Comma Separated Values) much more robustly than legacy .xls files, which are binary and difficult to generate programmatically without complex libraries like PHPSpreadsheet or PhpSpreadsheet. When you output text directly to the browser using echo, you are bypassing the binary structure required for true .xls files. Instead, the standard and most reliable solution is to export as CSV and ensure the headers are set correctly so Excel opens it with the right encoding.

The Limitations of Simple Text Output

The provided code constructs a string $data by concatenating fields separated by tabs (\t) and wrapping values in double quotes. This logic fails immediately if any field contains a newline character, which is common in fullname or bio fields. When Excel opens a file with embedded newlines inside quoted cells, the behavior depends on the specific version of Excel and the system encoding.

Furthermore, the code uses deprecated functions like mysql_query and mysql_fetch_assoc. These extensions were removed from PHP 7.0 due to security vulnerabilities (SQL injection risks) and lack of maintenance. Migrating to mysqli or PDO is not just an upgrade; it changes how you handle metadata and result sets, making the code future-proof.

Implementing a Robust CSV Export

To ensure each row value lands in a separate Excel cell, even if the data contains commas, tabs, or newlines, you must strictly adhere to RFC 4180 CSV standards. Every field containing a comma, double quote, newline, or carriage return must be enclosed in double quotes. Any existing double quotes inside the data must be escaped by doubling them (" becomes "").

Here is a modern implementation using PDO and prepared statements to prevent SQL injection while generating valid CSV output:

<?php
$host = 'localhost';
$db   = 'customers';
$user = 'root';
$pass = 'secret';
$charset = 'utf8mb4';

$dsn = "mysql:host=$host;dbname=$db;charset=$charset";
$options = [
    PDO::ATTR_ERRMODE            => PDO::ERRMODE_EXCEPTION,
    PDO::ATTR_DEFAULT_FETCH_MODE => PDO::FETCH_ASSOC,
    PDO::ATTR_EMULATE_PREPARES   => false,
];

try {
    $pdo = new PDO($dsn, $user, $pass, $options);
    
    // Use a prepared statement to safely handle the customer ID
    $query = "SELECT username, password, fullname FROM ecustomer_users WHERE fk_customer = :fk_customer LIMIT :limit";
    
    $stmt = $pdo->prepare($query);
    $stmt->execute([
        ':fk_customer' => $fk_customer,
        ':limit'       => 50 // Prevent memory exhaustion on large exports
    ]);

    if ($stmt->rowCount() === 0) {
        echo "no matching records found\n";
        exit;
    }

    // Start output buffering to capture the CSV string before sending headers
    ob_start();

    // Print header row first (optional, depending on your Excel needs)
    // echo "username,password,fullname\n"; 

    while ($row = $stmt->fetch()) {
        // Helper function to escape CSV values properly
        $escape_csv = function ($value) {
            if ($value === null || $value === '') {
                return '';
            }
            
            // Escape double quotes by doubling them
            $escaped = str_replace('"', '""', (string)$value);
            
            // Wrap in double quotes if the value contains special characters
            if (strpos($escaped, ',') !== false || 
                strpos($escaped, '"') !== false || 
                strpos($escaped, "\n") !== false || 
                strpos($escaped, "\r") !== false) {
                return '"' . $escaped . '"';
            }
            
            return $escaped;
        };

        // Join columns with commas
        echo implode(',', array_map($escape_csv, [$row['username'], $row['password'], $row['fullname']]));
        echo "\n";
    }

    $csv_data = ob_get_clean();

    // Set headers for download
    header("Content-Type: text/csv; charset=utf-8");
    header("Content-Disposition: attachment; filename=exportfile.csv");
    header("Pragma: no-cache");
    header("Expires: 0");

    echo $csv_data;

} catch (\PDOException $e) {
    error_log($e->getMessage());
    die('Database export failed.');
}

Why This Approach Works Better

The critical difference lies in the escaping logic. The original code attempted to handle empty strings by replacing them with tabs, which breaks CSV parsing if those rows are later imported back into a database or processed by other scripts. By returning an empty string for nulls and handling the quoting logic explicitly, we guarantee that Excel reads the data into distinct cells regardless of internal formatting in the source text.

Additionally, switching to text/csv with a Content-Disposition header ensures the browser treats the response as a downloadable file rather than attempting to render it inline. While .xls files exist, they require complex binary headers and structures that are rarely worth the effort compared to CSV, which Excel opens natively with full support for Unicode (UTF-8) when configured correctly. If your application runs on Laravel, you can utilize packages like laravel-excel to handle these exports server-side, offloading the memory-intensive string concatenation from the request loop and allowing for much larger datasets without hitting PHP's output buffer limits.

Our network