July 04, 2018

Export Mysql Data to CSV Using PHP and Mysqli

In previous post we learnt how to import CSV data into database. Today we will learn how to export mysql table data to CSV using PHP and Mysqli. We will export data from same employees table from previous post example.

Export Mysql Data to CSV Using PHP and Mysqli
Demo Download

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.
  • extract-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.

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

First we connect to database and fetch employees records. Then we fetch the first row from returned records and use array_map() to prepare csv headers. It will be the first row in our csv file as columns name. Then we set php headers  "Content-Type" and "Content-Disposition" to set the type of content and tell HTTP its a download response. We then open a file using php file function fopen() but in write-only stream output buffer and add all the fetched records to it. The php file function fputcsv() is used to insert a record to csv file. It accepts first parameter as the file handle and second parameter as the array of values to insert.

index.php

<!DOCTYPE html>
<html>
    <head>
        <title>Export Mysqli Data to CSV Using PHP and Mysqli - 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.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);

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