• Skip to primary navigation
  • Skip to main content
  • Skip to primary sidebar
The Electric Toolbox Blog

The Electric Toolbox Blog

Linux, Apache, Nginx, MySQL, Javascript and PHP articles

  • Applications
  • FCKEditor
  • Apache
  • Windows
  • Contact Us
Home / Send CSV data to the browser from MySQL & PHP with fputcsv

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

Check Out These Related posts:

  1. Fetching data using PHP and PDO with bound placeholders
  2. Ordering by specific field values with MySQL
  3. Load JSON data with jQuery, PHP and MySQL
  4. Create a CSV file from MySQL with PHP

Filed Under: MySql, PHP

Primary Sidebar

Categories

  • Apache
  • Applications
  • Article
  • Case Studies
  • Email Servers
  • FCKEditor
  • HTML And CSS
  • Javascript
  • Linux/Unix/BSD
  • Microsoft SQL Server
  • Miscellaneous Postings
  • MySql
  • Networking
  • Nginx Web Server
  • Offsite Articles
  • OSX
  • PHP
  • Quick Tips
  • RFC – Request for Comments
  • SilverStripe
  • VMWare
  • VPN
  • Windows
  • WordPress

Recent Posts

  • Vim Show Line Numbers
  • Add User To Group Linux
  • Chmod 777 Tutorial
  • How to Copy Directory Linux
  • Linux create user

Copyright © 2021. ElectricToolBox. All Rights Reserved.

  • Contact Us
  • Copyright Info
  • Privacy Policy
  • Sitemap