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

I covered how to use PHP's fputcsv without writing to a file in yesterday's post and look at a practical example today: fetching data from MySQL using PDO and sending it to the browser as a CSV, using the fputcsv() function.

Example data

The data used in this post is from my example table for MySQL post.

Example code

Substitute the [database name], [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.

error_reporting(E_ALL);
ini_set('display_errors', 1);

$dsn = "mysql:host=localhost;dbname=[database name]";
$username = "[username]";
$password = "[password]";

try {
    $pdo = new PDO($dsn, $username, $password);
} catch (PDOException $e) {
    // error handler
}

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

try {
    $stmt = $pdo->prepare("SELECT * FROM fruit ORDER BY name, variety");
    $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.

I'll cover how to buffer it into memory instead (for e.g. attaching to an email without having to write it out to file) in a subsequent post.

Example output

Example output from the fruit table:

fruit_id,name,variety
6,Apple,"Cox's Orange Pippin"
7,Apple,"Granny Smith"
1,Apple,"Red Delicious"
11,Banana,Burro
12,Banana,Cavendish
10,Banana,Plantain
5,Orange,Blood
3,Orange,Navel
9,Orange,Valencia
8,Pear,Anjou
4,Pear,Bartlett
2,Pear,Comice