Send CSV data to the browser from MySQL & PHP with PHP fputcsv function

Here, In this post i will show you how to fetch data from MySQL using PDO and sending it to the browser as a CSV, using the fputcsv() function.

Example code:

Substitute the dbname, username and password fields with the appropriate credentials and name for your database, and modify the database query to suit your use case. If you are using a different library than PDO, then obviously you'll need to change the database function calls to use that library instead.

for the sake of reliability, I am using magento's Database 'catalog_product_entity' table.

<?php

error_reporting(E_ALL);

ini_set('display_errors', 1);



$dsn = "mysql:host=localhost;dbname=magento";

$username = "root";

$password = "";



try {

    $pdo = new PDO($dsn, $username, $password);

} catch (PDOException $e) {

    // error handler

}



header('Content-type: text/csv');

header('Content-Disposition: attachment; filename="catalog_product.csv"');



try {

    $stmt = $pdo->prepare("SELECT * FROM catalog_product_entity ORDER BY entity_id");

    $stmt->execute();

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

    $header = true;

    while ($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

        if ($header) {

            fputcsv($output, array_keys($row));

            $header = false;

        }

        fputcsv($output, $row);

    }

    fclose($output);

} catch (PDOException $e) {

    // error handler

}

?>

The example code writes out a header line containing the column names, and then one line for each record.

If you prefer to write the data out to a file, remove the two header lines and change php://output to the filename.