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.

define.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</title>
        <meta content="text/html; charset=UTF-8" http-equiv="Content-Type"/>
        <link rel="stylesheet" href="css/style.css" />
    </head>
    <body>
        <div class="main-container">
            <div class="section">
                <div class="alert alert-blue">Click Export button to generate CSV.  <a href="export-data.php" class="btn btn-green pull-right">Export</a></div>
            </div>
        </div>
    </body>
</html>

export-data.php

<?php include("constants.php");
$dbcon = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD, DB_NAME) OR die(mysqli_connect_error());

$prepare = mysqli_query($dbcon,"SELECT fullname AS name,email,gender,designation FROM employees");
$employees = mysqli_fetch_all($prepare,MYSQLI_ASSOC);

$first_row = reset($employees);
$csv_headers  = array_map( function($name){
    return ucwords($name);
}, $first_row );

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: 0px;
}
body{
    background: #f0f0f0;
    font: normal normal 14px Open Sans,Verdana, Arial;
}
.main-container {
    max-width: 1024px;
    margin: 0px auto;
}
.section{
    padding: 15px;
    background: #fff;
}
.btn-green {
    background: #336611;
    border: 1px solid #225500;
    text-decoration: none;
    margin-top: -6px;
    color: #fff;
    display: inline-block;
    padding: 5px 10px;
    cursor: pointer;
    float: right;
}
Home