Tuesday, July 3, 2018

Export Mysql Data to CSV Using PHP and Mysqli

In previous post we learnt how to import CSV data to database. Today I will show you how to export database table data to CSV using PHP and Mysqli. We will export the same employees data to CSV.

Export Mysql Data to CSV Using PHP and Mysqli
Lets start with our same files config.php which will hold all the database configuration constants, index.php which contains the button to initiate csv generation, export-data.php  fetches the data from employees table and export to csv and style.css contains all the styles for index.php.

define.php

<?php
define("DB_HOST", "DATABASE HOST");  // Your database host
define("DB_NAME", "DATABASE NAME");  // Your database name
define("DB_USER", "USERNAME");  // Username for database 
define("DB_PASSWORD", "PASSWORD"); // Password for database
?>

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("define.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);
$col_names = [];

foreach($employees[0] as $name => $value){
    $col_names[] = ucwords($name);
}
// Set headers to download csv file.
header("Content-Type: application/csv; charset=utf-8");
header("Content-Disposition: attachment;filename=employees.csv");
$file = fopen("php://output","w"); // Open csv file in output buffer

fputcsv($file, $col_names); // Add column names to csv file
// Loop through all employees and add employee data csv
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;
}