July 04, 2018

Export MySQL Data to CSV in PHP

In this post we will show you how to export MySQL table data to CSV in PHP. We will export data from same employees table from previous post example.

Export Mysql Data to CSV Using PHP and Mysqli
Demo Download

CSV is abbreviation of Comma Separated Values which contains the table data in comma separated format. The functionality to export data from MySQL database table to CSV in PHP is useful when you want to backup your data or want to import it into another application which already has CSV import functionality. In this post we are going to demonstrate how you can easily export MySQL data to CSV file. We connect to database and fetch records from desired table and write it to csv output stream. You may also want to check out How to import CSV into MySQL Database in PHP.

So to export data from our employees table we will need following files:

  • constants.php: Contains database constants used for database connection.
  • index.php: Contains the HTML button that will trigger the export.
  • export-data.php: The file that will fetch data from employees table and prepares a CSV file for download.
  • style.css: Styles for our HTML page.


Add Constants for Database Connection

The constants.php contains database connection constants that we will be using in our export-data.php.

constants.php

<?php
define('DB_HOST', 'DATABASE_HOST');  // Your database host
define('DB_NAME', 'DATABASE_NAME');  // Your database name
define('DB_USER', 'DATABASE_USERNAME');  // Username for database
define('DB_PASSWORD', 'DATABASE_PASSWORD'); // Password for database
 

Create an HTML Page with Button To Download CSV

Create an HTML page and add a button for user to click for downloading CSV.

index.php

<!DOCTYPE html>
<html>
    <head>
        <title>Export MySQL Data to CSV in PHP - Demo</title>
        <meta content="text/html; charset=UTF-8" http-equiv="Content-Type"/>
        <link rel="stylesheet" href="css/style.css" />
    </head>
    <body>
        <div class="container">
            <div class="alert">
                Click Export button to generate CSV.  
                <a href="export-data.php" class="btn">Export</a>
            </div>  
        </div>
    </body>
</html>

Export Data to CSV File in PHP

Our export-data.php is  fetching records from employees table and initiating a download of CSV file. Lets see how this script is performing the export to CSV:

  1. First we connect to database and fetch employees records. 
  2. Then we fetch the first row from returned records and use array_map() to prepare csv headers. 
  3. It will be the first row in our CSV file as column name/header. 
  4. Then we set PHP headers  "Content-Type" and "Content-Disposition" to set the type of content and tell HTTP its a download response of a CSV file. 
  5. We then open an output stream using PHP file function fopen() with write-only flag. 
  6. We insert all the records from query to this output stream. The PHP file function fputcsv() is used to insert a record to CSV output stream. It accepts first parameter as the file handle and second parameter as the array of values to insert.

export-data.php

<?php
include 'constants.php';

$db_connection = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die(mysqli_connect_error());

$prepare = mysqli_query($db_connection, 'SELECT full_name AS name, email, gender, designation FROM employees');
$employees = mysqli_fetch_all($prepare, MYSQLI_ASSOC);

$csv_headers = array_map('ucwords', array_keys(reset($employees)) );

header('Content-Type: application/csv; charset=utf-8');
header('Content-Disposition: attachment;filename=employees.csv');

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

fputcsv($file, $csv_headers);

foreach($employees as $employee){
    fputcsv($file, $employee);
}
fclose($file);

Add CSS Styles

Add CSS styles for HTML page including styles for export button.

style.css

* {
    box-sizing: border-box;
}
html,body {
    margin: 0;
}
body {
    background-color: #f6f6f6;
    font-family: "Segoe UI", "Roboto", "Helvetica", sans-serif;
    font-size: 15px;
    font-weight: normal;
    font-style: normal;
}
.container {
    max-width: 1140px;
    width: 100%;
    margin-right: auto;
    margin-left: auto;
    padding-right: 15px;
    padding-left: 15px;
}
.alert {
    color: #ffffff;
    padding: 1rem;
    margin-bottom: 1rem;
    background: #00c0ef;
    border: 1px solid #00b0de;
    display: flex;
    align-items: center;
}
.btn {
    text-decoration: none;
    display: inline-block;
    padding: 5px 10px;
    cursor: pointer;
    font: inherit;
    border: 1px solid #eeeeee;
    background-color: #ffffff;
    margin-left: auto;
}

Home