July 04, 2018

Export MySQL Data to CSV in PHP

Exporting MySQL data to a CVS file is a useful feature for data sharing, data migration, keeping a backup, and reporting purposes. Learn how to export MySQL table data to CSV in PHP in simple and easy steps.

Export MySQL Data to CSV in PHP
Demo Download

CSV is an abbreviation of Comma Separated Values, which contains the table data in a comma separated format. The functionality to export data from a MySQL database to CSV in PHP is used when you want to back up your data or want to import it into another application that already has the Import CSV into MySQL Database in PHP functionality. This post demonstrates how to export MySQL data to a CSV file with working code. We will connect to the database, fetch records from the table we want to export data from, and write the data to the CSV output stream. We will export data from the same employees table from the previous post example.

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

  • constants.php: Contains database constants used for database connection.
  • index.html: Contains the HTML button that will trigger the export.
  • export-data.php: The file that will fetch data from the employees table and prepare 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 server-side export-data.php file.

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 a Button To Download CSV

Create an HTML page as a user interface and add a button for the user to initiate export upon click. The button on click will trigger a CSV download.

index.html

<!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"/>
        <meta content="width=device-width, initial-scale=1, maximum-scale=1" name="viewport" />
        <link rel="stylesheet" href="css/style.css"/>
    </head>
    <body>
        <section class="section py-4">
            <div class="container">
                <div class="alert">
                    Click Export button to generate CSV.
                    <a href="export-data.php" class="btn">Export</a>
                </div>
            </div>
        </section>
    </body>
</html>

Export Data to CSV File in PHP

Our export-data.php is fetching records from the employees table and initiating a file download. The following are the steps to export to CSV:

  • First, we connect to the database and fetch employee records.
  • Then we fetch the first row from the returned records and use array_map() to prepare headers.
  • It will be the first row in our file as the column name/header.
  • Then we set PHP headers  Content-Type and Content-Disposition to set the type of content and tell HTTP that it's a download response of a CSV file.
  • We then open an output stream using the PHP file function fopen() with the write-only flag.
  • We insert all the records from the query into this output stream. The PHP file function fputcsv() is used to insert a record into the output stream. It accepts the first parameter as the file handle and the 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());

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

$employees = mysqli_fetch_all($query, 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 for The Export Interface

Add CSS styles for the HTML page, including styles for the 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;
}
.py-4, .pt-4 {
    padding-top: 1rem;
}
.py-4, .pb-4 {
    padding-bottom: 1rem;
}
.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;
}

With these simple lines of PHP code, we can now quickly export MySQL data into a CSV file that can be downloaded. The code can be extended to add some filters to the HTML page and apply them before retrieving the data from the database table.

Home