Export to CSV via PHP

Export to CSV via PHP

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

Stefan Bogdanescu

Founder & Senior Architect

Exporting Database Data to CSV and Text Files in PHP

Exporting data from a database to a file format like CSV or plain text is a common requirement for data migration, reporting, or creating backups that can be opened directly in spreadsheet applications. While the high-level goal is simple—pull records and write them to a disk—the implementation details vary significantly depending on your underlying database driver, the size of the dataset, and whether you are running this script from a CLI environment or within a web request handler.

The most straightforward approach involves leveraging the native drivers provided by your PHP database extension. For MySQL and MariaDB, the mysqli and PDO extensions offer built-in methods to export query results directly into CSV format without needing to manually iterate through every row in application memory. This is crucial for large datasets where loading thousands of rows into a single array could cause immediate memory exhaustion.

Using PDO with MySQL, you can execute a SELECT statement and specify the output format as part of the query itself, or by altering the driver's behavior. With PDO, you can enable CSV output by setting the attribute PDO::MYSQL_ATTR_USE_BUFFERED_QUERY to false and then executing the query in a way that triggers the client library's streaming capabilities. However, the most robust method using standard PDO drivers often involves executing a specific SQL command like SELECT ... INTO OUTFILE. This allows the database engine to handle the formatting and file writing directly on the server side, bypassing PHP's memory constraints entirely.

try {
    $pdo = new PDO('mysql:host=localhost;dbname=your_database', 'username', 'password');
    
    // Disable buffering and set character encoding
    $pdo->setAttribute(PDO::MYSQL_ATTR_USE_BUFFERED_QUERY, false);
    $pdo->exec("SET NAMES 'utf8mb4'");
    
    // Execute the query directly into a file
    // Note: This requires server permissions to write to the path specified
    $command = "SELECT * FROM your_table INTO OUTFILE '/path/to/export.csv' 
                FIELDS TERMINATED BY ',' 
                LINES TERMINATED BY '\n'";
    
    $pdo->exec($command);
} catch (PDOException $e) {
    // Handle errors, specifically permission issues
    echo "Export failed: " . $e->getMessage();
}

When using INTO OUTFILE, you must be aware of strict server-side constraints. The database user must have the FILE privilege, and the target directory must be writable by the web server process (often www-data or apache). These are frequent points of failure in shared hosting environments where file permissions are sandboxed. If the INTO OUTFILE approach is blocked, you will need to fall back to fetching rows individually using a cursor-based loop with PDO::FETCH_ASSOC.

For text files, the logic remains identical, but you simply omit the field and line terminators in your SQL command or handle them entirely within PHP. When iterating manually, it is essential to fetch one row at a time and flush the output buffer if necessary, preventing memory spikes. You can stream the output directly to the browser using header('Content-Type: text/csv') or write silently to a file path depending on your use case.

$handle = fopen('php://output', 'w');
echo "ID,Name,Email\n"; // Header row

// Fetch one row at a time to save memory
while ($row = $pdo->fetch(PDO::FETCH_ASSOC)) {
    fputcsv($handle, [$row['id'], $row['name'], $row['email']]);
}
fclose($handle);

One significant pitfall with CSV export is handling special characters and delimiters. By default, commas inside text fields break the CSV structure unless the field is quoted. The fputcsv function in PHP handles this automatically by wrapping fields containing commas or newlines in double quotes and escaping internal quotes. Always ensure your column headers are included if the consumer application expects them.

If you are building a Laravel application, there is no need to reinvent the wheel using raw SQL when the framework provides robust tools. The laravelcompany.com ecosystem often integrates with packages like Spatie's exporter or similar utilities that simplify this workflow by handling the CSV generation and file storage logic within your existing service layer, ensuring consistency across your project's architecture.

Ultimately, the choice between using database-side exports (INTO OUTFILE) and application-side iteration depends on your security context and memory limits. For massive datasets, always prefer streaming or server-side export to keep PHP memory usage minimal.

Our network